Error while creating Linked server "Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server ‘LinkedServerName’"

  • Article

When we create linked server for SQL server 2000 on SQL server 2005/SQL server 2008 /SQL server 2008 R2 sometimes we see errors w.r.t schema.

In this blog, I will discuss on why we get this type of error and also the solution to fix the error message.

There is a well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server, you might get the below error.

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

More Information

https://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I had a chance to work with one of the DBA's from prestigious company where they were getting same error while creating the linked server for SQL Server 2000 on SQL server 2008 R2.

Error Message:

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

Main Cause of this issue:

· This problem occurs because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.

· May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.

· This step is not specific to SQL Server 2000 SP3 64-Bit, but applies to SQL Server 2000 SP3 32-Bit as well.

· "You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005/SQL server 2008/SQL server 2008 R2 client to a linked 32-bit SQL Server 2000 server" 

· Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded

Resolution steps:

· The system administrator should back up the master database before running Instcat.sql.

· Log on to the computer (SQL server 2000 server) by using a Windows account, whichis a member of the SQL Server sysadmin fixed server role.

· Click Run, type cmd.exe, and then click OK.

· At the command prompt, type one of the following commands, and then press ENTER

o   osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server)

o   osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql(For SQL account with default SQL server)

o   osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server)

o   osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server)

· Test to see that, you can run all distributed queries.

Reference Links

· You may receive an error message when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server

https://support.microsoft.com/default.aspx?scid=kb;en-us;906954#appliesto

· How to upgrade the catalog stored procedures

https://technet.microsoft.com/en-us/library/aa215459%28SQL.80%29.aspx

 

Author : Archana , SQL Developer Engineer , Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft