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:
- 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.
- Back up the master database
- Click Run, type cmd.exe, and then click OK.
- 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)
- Test to see that, you can run all distributed queries
Possible resolution Solution 2:
- 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
I actually tried both suggested resolutions, however only Solution 2 successfully worked for me