ServerLink Error x64 SQL Server Installation and x32 Version – Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO”

After setting up ServerLink from a x64 SQL Server 2008r2 installation to a x32 SQL Server 2000 installation when trying to join results from the x32 server into my x64 server I came across the error:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO".

A previous DBA had created a package to copy the data daily as a workaround for this error but was insufficient as the data is live.

A little google search later brought me to Raffaeu Bermuda’s page where he had encountered a similar issue with a SQL 2005 to a 2000 server.

The explanation for this error is that the stored procedure to process the results rowset isn’t available in the x32 server and therefore cannot communicate.

The solution was to create this stored procedure.

The stored procedure is just a copy from my x64 server’s master database and placed in the x32 server’s 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

This resolved the error and can now join the data successfully.

Source: http://blog.raffaeu.com/archive/2008/06/19/sql-2005-and-linked-server-cannot-obtain-the-schema-rowset.aspx