Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

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

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

Rate This
  • Comments 2

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

http://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

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

· How to upgrade the catalog stored procedures

http://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

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Hello thanks for the post

    I have two database server each X(SQL Server 2000) and Y(SQL Server 2008), I created a link server in Y server and I can see and navigate the

    between databases and tables. However, I cannot execute query it generates this error:

    ---------------------------------------------------------------------------------------------

    OLE DB provider "SQLNCLI10" for linked server "MY_LINKED_SERVER" returned message "Errore non specificato.".

    OLE DB provider "SQLNCLI10" for linked server "MY_LINKED_SERVER" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

    is this post ok in my case should i go through? I am a bit scared because I am

    new in microsoft technology.

    thanks

    Msg 7311, Level 16, State 2, Line 1

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

    ---------------------------------------------------------------------------------------------

  • 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.

Page 1 of 1 (2 items)