다음을 통해 공유

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

  • 아티클

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

Comments

  • Anonymous
    October 26, 2012
    The comment has been removed

  • Anonymous
    October 26, 2012
    Hello hamd, SQL 2000 is no longer supported. its difficult to say exactly what is going on. but can you try to create the linked server by the name of the SQL server itself. if you are creating a linked server in server Y to connect to X, name the linked server as X, and select the "SQL Server" option on that page. see if this helps.

  • Anonymous
    November 14, 2018
    Hi , Got any resolution for the below Error , This is on sql server 2008 r2 verion 64bit both instances are 64 bit , I tried many options but still the sql agent job fails with this error and this happens weekly once not specific to any day . The OLE DB provider "MSDASQL" for linked server "" reported an error. The provider reported an unexpected catastrophic failure. [SQLSTATE 42000] (Error 7399) Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "MSDASQL" for linked server "". The provider supports the interface, but returns a failure code when it is used. [SQLSTATE 42000] (Error 7311). The step failed.