While creating a linked server from SQL Server 2005 to SQL Server 2000, I got the following error when I tried to query the remote server.
OLE DB provider "SQLNCLI10" for linked server "XXXXXX" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "XXXXXX". The provider supports the interface, but returns a failure code when it is used.
After some initial research found that there is a Microsoft support page for this error but requires SP3 or higher on the 2000 instance and also requires us to run a particular SQL file called 'instcat.sql in the INSTALL directory for MSSQL. But such a change would require backups of the system databases. In case something went wrong with the SQL Server installation then the problem could get worse.
Ref : http://connect.microsoft.com/SQLServer/feedback/details/465959/unable-to-query-linked-sql-server-2000
Instead I found a small workaround that seems to have been successfully implemented by a lot of folks. This is not a solution that I came up with but saved me a lot of time so wanted to share it with the community.
Just create the following Stored Procedure on the SQL Server 2000 instance, in the MASTER database and GRANT EXECUTE to the Public.
CREATE PROCEDURE sp_tables_info_rowset_64
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
As noted earlier do not forget to GRANT EXECUTE on this SP to the public or whoever needs to use the linked server.
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC
Hope this helps........