Solutiion is from http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx
Issue with 64-bit SQL Server using SQL 2000 linked server
Recently we started adding SQL Server 2008 64-bit servers to our production set and we ran into the following issue. When we ran queries on a linked 2000 server, we were getting the following error:
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.
This article from MS website describes the issue pretty well, although it says the issue applies to 2005 but we are using 2008. As suggested, we ran the Instcat.sql file on our development system first, and we ended up getting errrors left and right, so we didn't dare to run it on our main production server. In the end, this workaround worked for us - we needed to create a procedure in the master database on the linked 2000 server. The proc is called sp_tables_info_rowset_64 and it is needed because it is called by 64-bit servers when running remote queries.
Here is the text of the proc in case you ever need to do the same, create it in the master database:
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.
This article from MS website describes the issue pretty well, although it says the issue applies to 2005 but we are using 2008. As suggested, we ran the Instcat.sql file on our development system first, and we ended up getting errrors left and right, so we didn't dare to run it on our main production server. In the end, this workaround worked for us - we needed to create a procedure in the master database on the linked 2000 server. The proc is called sp_tables_info_rowset_64 and it is needed because it is called by 64-bit servers when running remote queries.
Here is the text of the proc in case you ever need to do the same, create it in the master database:
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
go
