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!

Advertisements

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

  1. Thanks – that little update to master made a lot more sense than MS wanting me to rerun instcat.sql on my SQL 7 box.

  2. Thanks so much. Worked like a charm!

  3. Thanks so much for posting this.
    I created that proc and was successful in linking between SQL2005 64x and SQL2000 32x

    Thanks,
    Anukul

  4. That’s fantastic. It worked. Thanks

  5. Christopher Rannow

    THANK YOU THANK YOU THANK YOU!!!!!

    This was a real life saver in a pinch. I was having the same problem except with SQL 2000 running SP3. I know that I should update to SP4 and I will but I couldn’t right away since the server was under heavy use. This wrapper got me through in a pinch.

  6. This really helped! I can’t believe you wrote it like two years ago – I’m way behind the times.

  7. Pingback: Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!? « The Tech Notebook – My Take on Tech

  8. Pingback: links for 2009-06-05 « News to Me

  9. Works like to charm between 32 bit 2000 and 64 bit 2005. Much better than trying to figure out what instcat.sql does before running on a production box.

  10. Thank you very much it works on first time 🙂

  11. Pingback: Personal Weblog of John Wood » Blog Archive » Create Linked Server View of SQL Server 2000 32bit on 64bit SQL Server 2005

  12. Great solution. I wonder however if I create the proc on the master DB, how would it effect the upgrade process to the SPs listed above. I hope Microsoft’s upgrade script include a check and drop the wrapper proc. I hope it won’t cause the SP patch to fail when we upgrade.

  13. Worked well with SQL Server 2005 x64 and SQL Server 7.0

  14. Thanks for the great solution and for sharing the valuable info !!!

  15. Thanks, workes perfect.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s