Today I was trying to run a query from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Test machine through a Linked Server. When I tried running the query after creating the Linked Server, it throwed up the following error message.
OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX” 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, Procedure sp_test, Line 94
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “us-XXX-X-XXXXX”. The provider supports the interface, but returns a failure code when it is used.
While doing a some research, I found a small workaround for this, that seem to have been successfully implemented by many . **This is not a solution that I came up with but 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_name SYSNAME
,@table_schema SYSNAME = NULL
,@table_type NVARCHAR(255) = NULL
DECLARE @Result INT
SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name
Hope this helps you.