Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server " ". The provider supports the interface, but returns a failure code when it is used.

I was trying to run a job from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Production 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 "FOCUS" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "FOCUS" 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 "FOCUS". The provider supports the interface, but returns a failure code when it is used.

Since I had ran this query directly on PROD, I was pretty sure that the query works fine. Then I found the link from MS site while googling.

http://support.microsoft.com/kb/906954

Then I found that it basically needs a stored procedure (sp_tables_info_rowset_64) to fix the issue between 32bit and 64bit. The complete procedure is available in multiple sites. But the fix only requires few lines of it. The code is listed below.

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

MvM