Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?

We were having to use SSIS to pull data back and forth from an old SQL Server 7 server to our main production server which runs SQL Server 2005 SP2 x64 standard.

When I tried to create the linked server using:

 EXEC sp_addlinkedserver   
   @server='MySQLServer7'

Then I try

SELECT * FROM [MySQLServer7].[msdb].[dbo].[sysfiles] -- Or any other SQL

I get the following error

OLE DB provider "SQLNCLI" for linked server "MySQLServer7" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "MySQLServer7" 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 9
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "OSLO". The provider supports the interface, but returns a failure code when it is used.

Now the solution for connecting to SQL Server 2000 32-bit is to run instcat.sql as referenced at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

However, this isn’t really an option on SQL Server 7.0, luckily I found a very helpful post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1 by Marek Adamczuk explaining that it’s simply that 32-bit editions are missing a stored procedure on the master database called sp_tables_info_rowset_64.

Marek’s solution is to create a wrapper on the master database as follows (his code not mine):

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 works a treat – cheers Marek!