November 7, 2002 at 12:08 pm
I have 2 SQL 2000 servers that are linked S1 to S2. On S1 in QA I can issue a select statement to a table on S2 using fully qualified table name and it works. The problem is when I try to JOIN a table from S1 to a table in S2 I get the error message "Server: Msg 7313, Level 16, State 1, Line 3 Invalid schema or catalog specified for provider 'SQLOLEDB'. I did this exact same thing but S2 was a SQL 7.0 server and never had a problem. Also, I have tried the OLE DB driver for SQL and the OLE DB driver for ODBC. No Success. This should be an easy one?
November 7, 2002 at 12:31 pm
Can you post examples of the query that executed fine and the one that failed. I have had no issues right off but something may jump out if I can see what you have done.
November 7, 2002 at 1:26 pm
The results from this one work no errors "select top 10 * from S2.Cat1.dbo.table1" executed from S1 QA, S2 being the linked server. The results from this one return the error "SELECT table1.field1, s2_remote.field1 FROM S2.Cat1.dbo.table1 s2_remote INNER JOIN table1 ON s2_remote.field1 = table1.field1". The table on the linked server has the alias s2_remote. The table1 is on the local server. Just selecting the fields that I am joining on for this example. This is executed from QA on S1(the local server). Hope this helps. This is really bugging me.
November 7, 2002 at 1:54 pm
Ok, I looked around and cannot find anything on this except unable to do the first query.
What I would do is open Profiler and attach to the remote server. Then set to catch you query, look at SQL stmt begin and exceptions. See what statments run when the query hits the remote data source and that you query is geting there.
Also, I would try instead of using just table1 in the inner join use the 3 part fully qualified name and alias it to see what happens.
Finally try these queries and see if the data is an issue somehow
select * from S2.Cat1.dbo.table1
and
select field1 from S2.Cat1.dbo.table1
November 7, 2002 at 2:07 pm
I believe that using the fully qualified name should fix your problem.
November 7, 2002 at 4:21 pm
I would think that there is no difference between a straight select and a select with a join in the query in using the fully qualified table name. Thanks for your suggestions. Back to it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply