FIX: Add a 32-bit SQL Server 2000 as a Linked server in 64-bit SQL Server 2005

There are certain scenarios where we need to access an object (table, SP, etc) of remote server's database.
In such cases, we need to add that remote server as a Linked server in our SQL Server.

We can add a linked server by following path in Object Explorer of your SQL Server's Management Studio:

Server Objects >> Linked Servers; Right-click and add new linked server, and provide details of remote server you wish to link.

But if your remote server is a 32-bit SQL Server 2000, and your host server is 64-bit SQL Server 2005/2008, then generally you would be getting a following error while accessing any object (i.e. Table, SP, etc) of that remote server.

Query Execeution failed. Could not obtain the schema rowset "DBSCHEMA_TABLES_INFO" or similar message.

if you follow some articles over internet, they have suggested to execute some critical scripts to your remote server. This script file is of some 700KB, and performs lots of drop and create operation. This does not sound to be a good solution honestly.

The actual problem here is that, when you try to query an object of a remote sql server, it executes sp_tables_info_rowset in background. This happens without any issue if both the hosting and remote SQL server are of similar type.
But when you try to query an object of a remote sql server from a 64-bit connection, it attempts to execute sp_tables_info_rowset_64 sp, which obviously not a part of SQL Server 2000 installation.
So the simple workaround is to create this SP on remote SQL Server's master database, and this will simply a wrapper of sp_tables_info_rowset SP that already exist in remote SQL Server's master database.

Following is the script to create this SP:
Execute it in master database of remote sql server.

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

EXECUTE @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Once done, you should be able to query the database hosted on that remote server without any trouble.