Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

sql

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

Linking Oracle view to SQL Server express

Hi,

I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.

Is there a way to improve performance in querying?

Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.

Thanks a lot,

Stara

Which driver did you use ? Its long ago that I worked with Oracle linked servers, but you should use the Oracle driver which comes with the clint installation of Oracle rather than the MS one. You could also try to use an Openquery rather than the direct view as this statememt is directly executed on the Oracle system and is not further translated through the driver tier. Are you executing a complicate query whereas calculations are done on the Oracle or SQL Server side ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I have used MSDAORA. How do I use OpenQuery. Can you please provide an example?

Thanks,

Stara

|||

You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here. When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there.

Regards,

Mike

Linked View to SQL Server DB

I am using a linked view created in the SQL Server database. However, when
I
run it I get the following error.
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification (#0)
The help for this message indicates that it may be a network problem.
However, all other tables and views run correctly. This is the only one tha
t
fails. I have relinked, I have removed and recreated the link, and still
this one table view fails.
Any suggestions will be appreciated.Not sure what your other data source is but it looks like a
data type translation issue, not necessarily a network
issue. Try updating the ODBC driver - older versions don't
support newer data types.
If you are referring to something you are doing in MS
Access, you need to make sure to update your Jet drivers as
well.
-Sue
On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
<Outdone@.discussions.microsoft.com> wrote:

>I am using a linked view created in the SQL Server database. However, when
I
>run it I get the following error.
>ODBC--call failed.
>[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
>specification (#0)
>The help for this message indicates that it may be a network problem.
>However, all other tables and views run correctly. This is the only one th
at
>fails. I have relinked, I have removed and recreated the link, and still
>this one table view fails.
>Any suggestions will be appreciated.|||I have a similar problem (and I'm new to SQL Server). I created a view in
SQL Server. I assigned select permissions for a user ID on that view. I
then created an odbc connection so I could link that view in my Access
database. When I try to open the view, I get the 'ODBC--call failed' error.
I can access all the other linked tables within Access. Is there a problem
with the permissions set on the view, or is it an odbc problem?
Thanks,
Melanie
"Sue Hoegemeier" wrote:

> Not sure what your other data source is but it looks like a
> data type translation issue, not necessarily a network
> issue. Try updating the ODBC driver - older versions don't
> support newer data types.
> If you are referring to something you are doing in MS
> Access, you need to make sure to update your Jet drivers as
> well.
> -Sue
> On Wed, 5 Jul 2006 07:23:02 -0700, Outdone
> <Outdone@.discussions.microsoft.com> wrote:
>
>|||Really can't say. ODBC call failed is just a generic error
message. You could try turning on ODBC tracing on the client
to try to get more information on the error. You would want
to make sure to turn it back off after you get the error as
it will really slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Wed, 5 Jul 2006 13:39:01 -0700, Melanie O
<MelanieO@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have a similar problem (and I'm new to SQL Server). I created a view in
>SQL Server. I assigned select permissions for a user ID on that view. I
>then created an odbc connection so I could link that view in my Access
>database. When I try to open the view, I get the 'ODBC--call failed' error
.
>I can access all the other linked tables within Access. Is there a problem
>with the permissions set on the view, or is it an odbc problem?
>Thanks,
>Melanie
>"Sue Hoegemeier" wrote:
>

linked view in access don't use index

Hello,
I have a table with two field (name of the table : table_with_two_field)
field1, field2
when i create a simple view with no clause where:
create view dbo.view_on_field WITH VIEW_METADATA
as
select field1 from dbo.table_with_two_field
and i linked this wiew on access 2000 and i join with a local table on
field1 i have not response (field1 is a primary key on local and field1
have an index on the sql server table table_with_two_field)
in the trace i can see sql server send all the data : "select field1 from
dbo.view_one_field"
but when i do the same with local table and attached table i have a reponse
directly
can you help me
I'm not sure what precisely you are trying to do, but any time you
fetch data from the server and perform a join to a Access/Jet table,
ALL of the data is fetched from the server, and the join performed
locally by Jet. I am assuming that since you are performing a
heterogeneous join that you are not intending to update the data. What
I would suggest is to select from the SQLS table or view using a WHERE
clause to limit the result set, and dump it into a local Access/Jet
table, which should be very fast since you have fetched the data
locally and eliminated the heterogeneous join. The same local table
can be reused multiple times by deleting all of the rows before
fetching new ones.
--Mary
On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:

> Hello,
> I have a table with two field (name of the table : table_with_two_field)
> field1, field2
> when i create a simple view with no clause where:
>create view dbo.view_on_field WITH VIEW_METADATA
>as
> select field1 from dbo.table_with_two_field
>and i linked this wiew on access 2000 and i join with a local table on
>field1 i have not response (field1 is a primary key on local and field1
>have an index on the sql server table table_with_two_field)
>in the trace i can see sql server send all the data : "select field1 from
>dbo.view_one_field"
>but when i do the same with local table and attached table i have a reponse
>directly
>can you help me
|||Hello Mary,
I try to explain my problem, sorry for my english because i'm spain
when i use directly a linked table with a local table, i have data
immediatly, when I
see the trace, Access send every value field (use in join) from data from
the local table to the sqlserver.
The profiler trace send :
declare @.P1 int
set @.P1=2
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
select @.P1
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
Local table have 3 records
when i do a simple view like this
create view dbo.view as select field1 from dbo.table1
when i use directly this linked view with a local table,
the trace send :
SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft? Access etc..
and slqserver send millions records to access ...
Why the linked view and the linked table not have the same reaction ?
I can't do a view with more filter because every user have his owner local
table.
Thank you very much for your help.
I can
Rega1
"Mary Chipman [MSFT]" wrote:

> I'm not sure what precisely you are trying to do, but any time you
> fetch data from the server and perform a join to a Access/Jet table,
> ALL of the data is fetched from the server, and the join performed
> locally by Jet. I am assuming that since you are performing a
> heterogeneous join that you are not intending to update the data. What
> I would suggest is to select from the SQLS table or view using a WHERE
> clause to limit the result set, and dump it into a local Access/Jet
> table, which should be very fast since you have fetched the data
> locally and eliminated the heterogeneous join. The same local table
> can be reused multiple times by deleting all of the rows before
> fetching new ones.
> --Mary
> On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
> <toni@.discussions.microsoft.com> wrote:
>
|||I'm hazarding a guess here, but I suspect the reason why is because
Access cannot discover the schema for the view. Access caches schema
information for linked tables locally, so it can create the
parameterized prepared statement you are seeing in the first query.
Based on the Profiler output you posted, it *may* not do that for
views, so Access/Jet possibly has no idea of what the schema for the
view's underlying table is. Therefore it fetchs all of the data. You
might want to post in one of the msaccess forums in case the experts
there have a more definitive response.
--Mary
On Tue, 4 Oct 2005 05:35:02 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Hello Mary,
> I try to explain my problem, sorry for my english because i'm spain
> when i use directly a linked table with a local table, i have data
>immediatly, when I
> see the trace, Access send every value field (use in join) from data from
>the local table to the sqlserver.
>The profiler trace send :
>declare @.P1 int
>set @.P1=2
>exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
>"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
>select @.P1
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>Local table have 3 records
>when i do a simple view like this
>create view dbo.view as select field1 from dbo.table1
> when i use directly this linked view with a local table,
>the trace send :
>SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft Access etc..
>and slqserver send millions records to access ...
>Why the linked view and the linked table not have the same reaction ?
>I can't do a view with more filter because every user have his owner local
>table.
>Thank you very much for your help.
>I can
>Rega1
>
>
>
>"Mary Chipman [MSFT]" wrote:
sql

servers and partitioned view?

Hi,
I have 2 database servers running W2K/SQL 2000 with Gigabit Ethernet between
them. Server1 contains 2003 and 2002 data in one database. Server2 contains
2001 data in one database.
I have an web application that points to Server1 all the time. So, I created
a linked server from Server1 to add Server2 in. I then created Server1.VIEWS
of all tables from Server2.database.dbo.table_name...
The problem is that accessing data via Server1.VIEWS from the web
application is very slow. Reports aren't performing for 2001 data
physicially stored on Server2. The reports aggregate millions of rows on
each table.
what are ways to improve query performance via linked servers? I still want
to keep one web application pointing to one physicially server, if possible.
Thanks!
HHIf you haven't done so already, run a trace against the database and try to
index the tables on server2 based on the trace.
For some of the more common reports, if the data is aggregated maybe you can
bypass some of the work SQL Server has to do by creating tables with the
aggregated data in it.
You could try a solution using Analysis Services (this solution would
require a lot of FTE hours)|||Our relational reporting systems works great when the data is local. Reports
usually run within seconds. Only the set of data physically stored on
another machine via linked server performs severely. I didn't expect it to
be this slow because the two machine are on the same Gigabit Ether LAN. It
could be because the query is running on Machine1, but it has to pull all
data from Machine2 over first' It'd be ideal if queries are being run on
Machine2, and only results get transferred over the wire.
Thanks!
HH
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:eZhKrsJmDHA.964@.TK2MSFTNGP10.phx.gbl...
> If you haven't done so already, run a trace against the database and try
to
> index the tables on server2 based on the trace.
> For some of the more common reports, if the data is aggregated maybe you
can
> bypass some of the work SQL Server has to do by creating tables with the
> aggregated data in it.
> You could try a solution using Analysis Services (this solution would
> require a lot of FTE hours)
>

server, stored procedure and Views (Heterogeneous Errors)

I have linked to another SQL Instance, created a view and a stored procedure
to access it. This works great through MS Query, but not via SQL Triggers or
through calling through code in another application.
The views and sprocs create without errors, it's only when running them.
Any Ideas?Ray
Can you show us how you call the statemnet?
"Ray" <rayc@.rsc.com> wrote in message
news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>I have linked to another SQL Instance, created a view and a stored
>procedure
> to access it. This works great through MS Query, but not via SQL Triggers
> or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?|||you will have to use the four part naming convention
linkedservername.database.owner.object
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ray" wrote:

> I have linked to another SQL Instance, created a view and a stored procedu
re
> to access it. This works great through MS Query, but not via SQL Triggers
or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?

server Woes

Whilst experienced with SQL Server in general, I have had no exposure to
linked servers. I have, therefore, been "having a play" with a view to an
upcoming project.
The project will involve updating a database on an external server (another
company) via triggers based on that in our own company (update in realtime).
The databases will be different and on different networks thus replication
is out.
I have managed to link test servers together and expose only the tables on
each database based on the access permissions of the SQL logins used. I can
select data from each and I can also insert or update from one to the other.
The problem I have is when adding triggers to one table that changes data on
the other, the system either hangs or I get error messages. Below is coding
used for triggers etc along with the error message (I initially used just a
trigger but read somewhere that DTC does not react well to such, hence a
seperate sp that is called into play from within the trigger).
******************
TRIGGER:
create trigger trg_CustInserts
on tbl_Customer
for insert
as
declare @.Name varchar(100)
set @.Name = (select Forname+' '+Surname from inserted)
exec catdb.dbo.usp_CustomerInserts @.Name
STORED PROCEDURE:
create procedure usp_CustomerInserts @.Name varchar(100)
as
insert test.[Remote Database].dbo.tblUsers (UserName)
Select @.Name
COMMAND RUN TO POPULATE TABLE:
insert tbl_Customer (Forname,Surname)
values ('Johnny','Rotten')
ERROR MESSAGE:
Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
*************
I followed the instructions found on http://support.microsoft.com/kb/839279
in order to enable DTC etc but the problem remains.
Any advice would be appreciated.
Regards
DazSo you're willing to tie your database availability to the external
partner's database availability? If the partner database is down, your
trigger will fail causing downtime on your database. Have you considered a
more loosely coupled solution?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"news.microsoft.com" <Post2Group@.Only.com> wrote in message
news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
> Whilst experienced with SQL Server in general, I have had no exposure to
> linked servers. I have, therefore, been "having a play" with a view to an
> upcoming project.
> The project will involve updating a database on an external server
> (another
> company) via triggers based on that in our own company (update in
> realtime).
> The databases will be different and on different networks thus replication
> is out.
> I have managed to link test servers together and expose only the tables on
> each database based on the access permissions of the SQL logins used. I
> can
> select data from each and I can also insert or update from one to the
> other.
> The problem I have is when adding triggers to one table that changes data
> on
> the other, the system either hangs or I get error messages. Below is
> coding
> used for triggers etc along with the error message (I initially used just
> a
> trigger but read somewhere that DTC does not react well to such, hence a
> seperate sp that is called into play from within the trigger).
> ******************
> TRIGGER:
> create trigger trg_CustInserts
> on tbl_Customer
> for insert
> as
> declare @.Name varchar(100)
> set @.Name = (select Forname+' '+Surname from inserted)
> exec catdb.dbo.usp_CustomerInserts @.Name
> STORED PROCEDURE:
> create procedure usp_CustomerInserts @.Name varchar(100)
> as
> insert test.[Remote Database].dbo.tblUsers (UserName)
> Select @.Name
> COMMAND RUN TO POPULATE TABLE:
> insert tbl_Customer (Forname,Surname)
> values ('Johnny','Rotten')
> ERROR MESSAGE:
> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> *************
> I followed the instructions found on
> http://support.microsoft.com/kb/839279
> in order to enable DTC etc but the problem remains.
> Any advice would be appreciated.
> Regards
> Daz
>|||Remus
I am just looking into possibilities of linked servers at the moment. The
goal in the trigger would be to check if the table is available on the
linked server first. If so, transfer the data, in not put it in a holding
area ready for transfer.
I really need to overcome my lack of knowledge regards linked servers and
the errors I am getting. In a nutshell, I am taking things one step at a
time.
Any ideas on how to overcome this annoying error would be useful to me.
Regards
Daz
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
> So you're willing to tie your database availability to the external
> partner's database availability? If the partner database is down, your
> trigger will fail causing downtime on your database. Have you considered a
> more loosely coupled solution?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>|||What SQL versions are we talking about?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Dazza The Fat" <Post2Group@.Only.com> wrote in message
news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Remus
> I am just looking into possibilities of linked servers at the moment. The
> goal in the trigger would be to check if the table is available on the
> linked server first. If so, transfer the data, in not put it in a holding
> area ready for transfer.
> I really need to overcome my lack of knowledge regards linked servers and
> the errors I am getting. In a nutshell, I am taking things one step at a
> time.
> Any ideas on how to overcome this annoying error would be useful to me.
> Regards
> Daz
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||SQL 2000
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:uyHdPxWIHHA.1248@.TK2MSFTNGP03.phx.gbl...
> What SQL versions are we talking about?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Dazza The Fat" <Post2Group@.Only.com> wrote in message
> news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
>|||Perfect Match Finder
http://www.max-online.biz/idevaffil...iate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffil...iate.php?id=804
For further details email me at maxonline.sunil@.gmail.com

server Woes

Whilst experienced with SQL Server in general, I have had no exposure to
linked servers. I have, therefore, been "having a play" with a view to an
upcoming project.
The project will involve updating a database on an external server (another
company) via triggers based on that in our own company (update in realtime).
The databases will be different and on different networks thus replication
is out.
I have managed to link test servers together and expose only the tables on
each database based on the access permissions of the SQL logins used. I can
select data from each and I can also insert or update from one to the other.
The problem I have is when adding triggers to one table that changes data on
the other, the system either hangs or I get error messages. Below is coding
used for triggers etc along with the error message (I initially used just a
trigger but read somewhere that DTC does not react well to such, hence a
seperate sp that is called into play from within the trigger).
******************
TRIGGER:
create trigger trg_CustInserts
on tbl_Customer
for insert
as
declare @.Name varchar(100)
set @.Name = (select Forname+' '+Surname from inserted)
exec catdb.dbo.usp_CustomerInserts @.Name
STORED PROCEDURE:
create procedure usp_CustomerInserts @.Name varchar(100)
as
insert test.[Remote Database].dbo.tblUsers (UserName)
Select @.Name
COMMAND RUN TO POPULATE TABLE:
insert tbl_Customer (Forname,Surname)
values ('Johnny','Rotten')
ERROR MESSAGE:
Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
*************
I followed the instructions found on http://support.microsoft.com/kb/839279
in order to enable DTC etc but the problem remains.
Any advice would be appreciated.
Regards
Daz
So you're willing to tie your database availability to the external
partner's database availability? If the partner database is down, your
trigger will fail causing downtime on your database. Have you considered a
more loosely coupled solution?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"news.microsoft.com" <Post2Group@.Only.com> wrote in message
news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
> Whilst experienced with SQL Server in general, I have had no exposure to
> linked servers. I have, therefore, been "having a play" with a view to an
> upcoming project.
> The project will involve updating a database on an external server
> (another
> company) via triggers based on that in our own company (update in
> realtime).
> The databases will be different and on different networks thus replication
> is out.
> I have managed to link test servers together and expose only the tables on
> each database based on the access permissions of the SQL logins used. I
> can
> select data from each and I can also insert or update from one to the
> other.
> The problem I have is when adding triggers to one table that changes data
> on
> the other, the system either hangs or I get error messages. Below is
> coding
> used for triggers etc along with the error message (I initially used just
> a
> trigger but read somewhere that DTC does not react well to such, hence a
> seperate sp that is called into play from within the trigger).
> ******************
> TRIGGER:
> create trigger trg_CustInserts
> on tbl_Customer
> for insert
> as
> declare @.Name varchar(100)
> set @.Name = (select Forname+' '+Surname from inserted)
> exec catdb.dbo.usp_CustomerInserts @.Name
> STORED PROCEDURE:
> create procedure usp_CustomerInserts @.Name varchar(100)
> as
> insert test.[Remote Database].dbo.tblUsers (UserName)
> Select @.Name
> COMMAND RUN TO POPULATE TABLE:
> insert tbl_Customer (Forname,Surname)
> values ('Johnny','Rotten')
> ERROR MESSAGE:
> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> *************
> I followed the instructions found on
> http://support.microsoft.com/kb/839279
> in order to enable DTC etc but the problem remains.
> Any advice would be appreciated.
> Regards
> Daz
>
|||Remus
I am just looking into possibilities of linked servers at the moment. The
goal in the trigger would be to check if the table is available on the
linked server first. If so, transfer the data, in not put it in a holding
area ready for transfer.
I really need to overcome my lack of knowledge regards linked servers and
the errors I am getting. In a nutshell, I am taking things one step at a
time.
Any ideas on how to overcome this annoying error would be useful to me.
Regards
Daz
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
> So you're willing to tie your database availability to the external
> partner's database availability? If the partner database is down, your
> trigger will fail causing downtime on your database. Have you considered a
> more loosely coupled solution?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>
|||What SQL versions are we talking about?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Dazza The Fat" <Post2Group@.Only.com> wrote in message
news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Remus
> I am just looking into possibilities of linked servers at the moment. The
> goal in the trigger would be to check if the table is available on the
> linked server first. If so, transfer the data, in not put it in a holding
> area ready for transfer.
> I really need to overcome my lack of knowledge regards linked servers and
> the errors I am getting. In a nutshell, I am taking things one step at a
> time.
> Any ideas on how to overcome this annoying error would be useful to me.
> Regards
> Daz
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
|||SQL 2000
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:uyHdPxWIHHA.1248@.TK2MSFTNGP03.phx.gbl...
> What SQL versions are we talking about?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Dazza The Fat" <Post2Group@.Only.com> wrote in message
> news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com

server Woes

Whilst experienced with SQL Server in general, I have had no exposure to
linked servers. I have, therefore, been "having a play" with a view to an
upcoming project.
The project will involve updating a database on an external server (another
company) via triggers based on that in our own company (update in realtime).
The databases will be different and on different networks thus replication
is out.
I have managed to link test servers together and expose only the tables on
each database based on the access permissions of the SQL logins used. I can
select data from each and I can also insert or update from one to the other.
The problem I have is when adding triggers to one table that changes data on
the other, the system either hangs or I get error messages. Below is coding
used for triggers etc along with the error message (I initially used just a
trigger but read somewhere that DTC does not react well to such, hence a
seperate sp that is called into play from within the trigger).
******************
TRIGGER:
create trigger trg_CustInserts
on tbl_Customer
for insert
as
declare @.Name varchar(100)
set @.Name = (select Forname+' '+Surname from inserted)
exec catdb.dbo.usp_CustomerInserts @.Name
STORED PROCEDURE:
create procedure usp_CustomerInserts @.Name varchar(100)
as
insert test.[Remote Database].dbo.tblUsers (UserName)
Select @.Name
COMMAND RUN TO POPULATE TABLE:
insert tbl_Customer (Forname,Surname)
values ('Johnny','Rotten')
ERROR MESSAGE:
Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
*************
I followed the instructions found on http://support.microsoft.com/kb/839279
in order to enable DTC etc but the problem remains.
Any advice would be appreciated.
Regards
DazSo you're willing to tie your database availability to the external
partner's database availability? If the partner database is down, your
trigger will fail causing downtime on your database. Have you considered a
more loosely coupled solution?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"news.microsoft.com" <Post2Group@.Only.com> wrote in message
news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
> Whilst experienced with SQL Server in general, I have had no exposure to
> linked servers. I have, therefore, been "having a play" with a view to an
> upcoming project.
> The project will involve updating a database on an external server
> (another
> company) via triggers based on that in our own company (update in
> realtime).
> The databases will be different and on different networks thus replication
> is out.
> I have managed to link test servers together and expose only the tables on
> each database based on the access permissions of the SQL logins used. I
> can
> select data from each and I can also insert or update from one to the
> other.
> The problem I have is when adding triggers to one table that changes data
> on
> the other, the system either hangs or I get error messages. Below is
> coding
> used for triggers etc along with the error message (I initially used just
> a
> trigger but read somewhere that DTC does not react well to such, hence a
> seperate sp that is called into play from within the trigger).
> ******************
> TRIGGER:
> create trigger trg_CustInserts
> on tbl_Customer
> for insert
> as
> declare @.Name varchar(100)
> set @.Name = (select Forname+' '+Surname from inserted)
> exec catdb.dbo.usp_CustomerInserts @.Name
> STORED PROCEDURE:
> create procedure usp_CustomerInserts @.Name varchar(100)
> as
> insert test.[Remote Database].dbo.tblUsers (UserName)
> Select @.Name
> COMMAND RUN TO POPULATE TABLE:
> insert tbl_Customer (Forname,Surname)
> values ('Johnny','Rotten')
> ERROR MESSAGE:
> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> *************
> I followed the instructions found on
> http://support.microsoft.com/kb/839279
> in order to enable DTC etc but the problem remains.
> Any advice would be appreciated.
> Regards
> Daz
>|||Remus
I am just looking into possibilities of linked servers at the moment. The
goal in the trigger would be to check if the table is available on the
linked server first. If so, transfer the data, in not put it in a holding
area ready for transfer.
I really need to overcome my lack of knowledge regards linked servers and
the errors I am getting. In a nutshell, I am taking things one step at a
time.
Any ideas on how to overcome this annoying error would be useful to me.
Regards
Daz
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
> So you're willing to tie your database availability to the external
> partner's database availability? If the partner database is down, your
> trigger will fail causing downtime on your database. Have you considered a
> more loosely coupled solution?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used. I
>> can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes data
>> on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used just
>> a
>> trigger but read somewhere that DTC does not react well to such, hence a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> The operation could not be performed because the OLE DB provider
>> 'SQLOLEDB' was unable to begin a distributed transaction.
>> [OLE/DB provider returned message: New transaction cannot enlist in the
>> specified transaction coordinator. ]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>> *************
>> I followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>|||What SQL versions are we talking about?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Dazza The Fat" <Post2Group@.Only.com> wrote in message
news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Remus
> I am just looking into possibilities of linked servers at the moment. The
> goal in the trigger would be to check if the table is available on the
> linked server first. If so, transfer the data, in not put it in a holding
> area ready for transfer.
> I really need to overcome my lack of knowledge regards linked servers and
> the errors I am getting. In a nutshell, I am taking things one step at a
> time.
> Any ideas on how to overcome this annoying error would be useful to me.
> Regards
> Daz
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> So you're willing to tie your database availability to the external
>> partner's database availability? If the partner database is down, your
>> trigger will fail causing downtime on your database. Have you considered
>> a more loosely coupled solution?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
>> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used. I
>> can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes
>> data on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used
>> just a
>> trigger but read somewhere that DTC does not react well to such, hence a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> The operation could not be performed because the OLE DB provider
>> 'SQLOLEDB' was unable to begin a distributed transaction.
>> [OLE/DB provider returned message: New transaction cannot enlist in the
>> specified transaction coordinator. ]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>> *************
>> I followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>>
>|||SQL 2000
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:uyHdPxWIHHA.1248@.TK2MSFTNGP03.phx.gbl...
> What SQL versions are we talking about?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Dazza The Fat" <Post2Group@.Only.com> wrote in message
> news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> Remus
>> I am just looking into possibilities of linked servers at the moment.
>> The goal in the trigger would be to check if the table is available on
>> the linked server first. If so, transfer the data, in not put it in a
>> holding area ready for transfer.
>> I really need to overcome my lack of knowledge regards linked servers and
>> the errors I am getting. In a nutshell, I am taking things one step at a
>> time.
>> Any ideas on how to overcome this annoying error would be useful to me.
>> Regards
>> Daz
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
>> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> So you're willing to tie your database availability to the external
>> partner's database availability? If the partner database is down, your
>> trigger will fail causing downtime on your database. Have you considered
>> a more loosely coupled solution?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
>> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure
>> to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used.
>> I can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes
>> data on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used
>> just a
>> trigger but read somewhere that DTC does not react well to such, hence
>> a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> The operation could not be performed because the OLE DB provider
>> 'SQLOLEDB' was unable to begin a distributed transaction.
>> [OLE/DB provider returned message: New transaction cannot enlist in the
>> specified transaction coordinator. ]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>> *************
>> I followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>>
>>
>|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com

server view performance worse than not using view

Consider pseudo-DDL created on an "archive" machine, that wants to get data
from the "live" machine.
CREATE VIEW AllTransactions AS
SELECT * FROM Tranasction_Archived
UNION ALL
SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
Now ideally, you can query the Transactions view on the archive machine:
SELECT *
FROM AllTransactions t
INNER JOIN Customers c
ON t.cid = c.cid
and get presented a unified result set. But it turns out this performance is
horrible.
But, if you simply change your query to:
SELECT *
FROM Transactions_Archived t
INNER JOIN Customers c
ON t.cid = c.cid
UNION ALL
SELECT *
FROM LinkedServerToLive.Northwind.dbo.Transactions t
INNER JOIN Customers c
ON t.cid = c.cid
You will get a phenominal performance boost. All i did was break out what
the view contained,
and did the UNION ALL at the query level rather than abstract the complexity
into a view.
i am using SQL2000, is this a known optimizer failing that is fixed in
SQL2005?What are the differences in the execution plans? Are indexes being used to
their full advantage on the joins for both queries?
"Ian Boyd" wrote:

> Consider pseudo-DDL created on an "archive" machine, that wants to get dat
a
> from the "live" machine.
> CREATE VIEW AllTransactions AS
> SELECT * FROM Tranasction_Archived
> UNION ALL
> SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
> Now ideally, you can query the Transactions view on the archive machine:
> SELECT *
> FROM AllTransactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> and get presented a unified result set. But it turns out this performance
is
> horrible.
> But, if you simply change your query to:
> SELECT *
> FROM Transactions_Archived t
> INNER JOIN Customers c
> ON t.cid = c.cid
> UNION ALL
> SELECT *
> FROM LinkedServerToLive.Northwind.dbo.Transactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> You will get a phenominal performance boost. All i did was break out what
> the view contained,
> and did the UNION ALL at the query level rather than abstract the complexi
ty
> into a view.
> i am using SQL2000, is this a known optimizer failing that is fixed in
> SQL2005?
>
>|||My understanding, from a logical perspective, is this (see disclaimer
below)...
Regardless of your DBMS product, if you are using Oracle DB links or Linked
Servers in SQL Server, or some other artificial method to make DatabaseA
access a table in DatabaseB as if the tables were in the same database, then
the standard tuning mechanisms can no longer be used.
Basically, DatabaseA knows nothign about the tables in DatabaseB, and the
best it can do is link one column in the source database to the result set
in the remote database. IF the remote database has a view, the view cannot
be broken down efficiently into its parts, as it would if the databases were
the same. The remote database ends up processing the entire view in order
to return one row (although it may use an index based on criteria passed).
If the view were in the same database, then the tuning engine would
essentially rewrite the SQL and process the individual joins more
efficiently.
Think of it like going to the library and getting 5 books that you want, as
compared to submitting 5 requests for someone else to get the book, all of
which get handled seperately.
As usual, I am speaking regarding my own limited understanding of the SQL
Server engine. If anyone knows differently, or can explain it better,
please do.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23wziZJfIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Consider pseudo-DDL created on an "archive" machine, that wants to get
data
> from the "live" machine.
> CREATE VIEW AllTransactions AS
> SELECT * FROM Tranasction_Archived
> UNION ALL
> SELECT * FROM LinkedServerToLive.Northwind.dbo.Transactions
> Now ideally, you can query the Transactions view on the archive machine:
> SELECT *
> FROM AllTransactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> and get presented a unified result set. But it turns out this performance
is
> horrible.
> But, if you simply change your query to:
> SELECT *
> FROM Transactions_Archived t
> INNER JOIN Customers c
> ON t.cid = c.cid
> UNION ALL
> SELECT *
> FROM LinkedServerToLive.Northwind.dbo.Transactions t
> INNER JOIN Customers c
> ON t.cid = c.cid
> You will get a phenominal performance boost. All i did was break out what
> the view contained,
> and did the UNION ALL at the query level rather than abstract the
complexity
> into a view.
> i am using SQL2000, is this a known optimizer failing that is fixed in
> SQL2005?
>|||"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:B88552FB-15AD-443B-ACC9-8A8269741E50@.microsoft.com...
> What are the differences in the execution plans? Are indexes being used to
> their full advantage on the joins for both queries?
It looks as though in the slow case, SQL Server is performing tens of
thounds of executes on a single-row returning query, verses bringing back
tens of thousands of rows.
i would have posted the showplan_text outputs, but it did not show the
number of executes or rows returned through each step.

server to SQL-Server Question

I've been trying to work with Linked Servers for the first time, with
mixed success. My problem right now is in trying to write a View using
a linked server. The linked server is another SQL-Server, and it was
set up using the 'Enterprise Manager' interface. When the server was
created, the name was given as the full server address -
name.pyr.ec.gc.ca.

When I try and use this name in a new view, I enter the name in the
query as [name.pyr.ec.gc.ca].database.dbo.table. SQL-Server rewrites
this name as name.[pyr.ec.gc.ca.database].dbo.table table_1

I'm sure this is me not understanding something about using linked
servers, but it seems strange to me none the less. Is there a way for
me to create the linked server using sp_addlinkedserver, which would
not require the full server address as the linked server name? Or is my
view syntax not correct? Or can I just not create the query as a view?

I've looked around the new groups, but have no answers yet. Any help
would be much appreciated.

Timnever mind....finally found a single entry in BOL that explained it.

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>

server to Oracle on Unix

Hi all!
I have a SQL Server from which I need to query a view in a Oracle database.
I have created a linked server connection to the Oracle machine. Oracle,
version 9i, is running on Unix.
Now when I try to use the linked server connection I have some problems.
When I expand the linked server and try to see the tables and views I
sometimes get an error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Sometimes everything works just fine and sometimes I get the error message
above. When the developers are trying to query the Oracle view they
sometimes get the same error and sometimes the queries works just fine.
The thing is that I don't know anything about Unix so I don't know if there
is something I can do on that side.
Any help or suggestions would be greatly appreciated.
A few things you can try:
When the developers are querying Oracle, they can turn on
trace flag 7300 to get a more detailed error message. In
Query Analyzer, execute the following:
Dbcc traceon (7300,3604)
and then try executing the queries against Oracle.
Also, pay attention to the time it takes to execute the
queries when they fail and when they are successful. You
could be experiencing remote query timeouts. You can try
increasing the setting using sp_configure. You can find more
information in books online on using sp_configure.
You will also want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 16 Nov 2004 12:18:31 +0100, "Jaana"
<jaana.lehtonen@.banverket.se> wrote:

>Hi all!
>I have a SQL Server from which I need to query a view in a Oracle database.
>I have created a linked server connection to the Oracle machine. Oracle,
>version 9i, is running on Unix.
>Now when I try to use the linked server connection I have some problems.
>When I expand the linked server and try to see the tables and views I
>sometimes get an error message.
>Error 7399: OLE DB provider 'MSDAORA' reported an error.
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>Sometimes everything works just fine and sometimes I get the error message
>above. When the developers are trying to query the Oracle view they
>sometimes get the same error and sometimes the queries works just fine.
>The thing is that I don't know anything about Unix so I don't know if there
>is something I can do on that side.
>Any help or suggestions would be greatly appreciated.
>
>