Linked Server Error “Cannot obtain the schema rowset”

I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008  & SQL server 2008 R2.

The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”

After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.

 

Main Cause of this issue:

  • Problem may occur 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.
  • 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. I don’t think I ever done this.

Possible resolution Solution 1:

  1. Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
  2. Back up the master database
  3. Click Run, type cmd.exe, and then click OK.
  4. At the command prompt, type one of the following commands, and then press ENTER
 osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server) 
Linked server error
  • Test to see that, you can run all distributed queries

Reference Links

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

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

 

Possible resolution Solution 2:

  1. Create the following stored procedure on your linked SQL Server 2000 instance
USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

2. GRANT EXECUTE the procedure to the Public in the MASTER database

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Reference Links

http://blogs.xtivia.com/home/-/blogs/21924

I actually tried both suggested resolutions, however only Solution 2 successfully worked for me

fixing link server problem for SQL Server 2000

1 comment for “Linked Server Error “Cannot obtain the schema rowset”

  1. Meas Dymong
    October 11, 2016 at 6:55 am

    Thank you .
    It’s working correctly.

Leave a Reply

Your email address will not be published. Required fields are marked *