SQL 2005 Linked Servers  
Author Message
SteveWSR





PostPosted: Top

SQL Server >> SQL 2005 Linked Servers

I have attached two sql2000 datbases to sql2005 with compatibility 8 truned
on. The problem is when establishing a linked server connection to the
sql2005 server acrossed the two datbases I get the followin error:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server ".\sql2005". The provider supports the interface,
but returns a failure code when it is used.

Existing knowledge base documentation points to sql2000 sp3 or sp4. Is there
a known problem with the compatibility mode datbases and linked servers in
sql2005?

The reason I ask is that when I switch to compatibility mode 9 everything
works as expected (except that I still have to test the rest of the
application for errors)

Also it seems that when creating stored procedures (in compatibility mode 8)
that the table references are now fully checked whereas in sql2000 it
appeared that if there was a create table statement in the procedure the
following table references were delayed until execution time. Is this a know
issue/change?

SQL Server161