24
Sep

How to Link a 64-bit SQL Server to a 32-bit SQL Server

There is a bug in SQL Server 2008 and 2005.  When you try and link a 64 bit server to a 32 bit server, the select of data can fail.   But the fail is not obvious.

When you say “select * from linkedserver.mydbname.dbo.table” it will return a message about stored procedures such as:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.

This is because the 64-bit calling server is looking for a 64 bit procedure list on the serving server (where the data sits) – but Microsoft forgot to include this in the 32 bit releases of the software – doh!.

To overcome this, simply create the following function in the master database on the 32 bit server.  Note, if there is ever a chance that the 32-bit servers will be linked to a 32 bit server, it is worth creating this when SQL is first installed (or create it now, and never hit the link bug):

use master
Go

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

/********************************************************************************************************************/

/*                                                sp_tables_info_rowset_64                                          */

/*                                                                                                                  */

/* Version : 1.00 –  27-Mar-2012                                                                                    */

/*                                                                                                                  */

/* This dummy stored procedure wrapper is required on 32-bit SQL Server instances which are below the version of a  */

/* connected in SQL Server instance (i.e where 2008 connects to 2005, or 2005 connects to 2000) to overcome the SQL */

/* server bug detailed in Microsoft KB item kb/906954                                                               */

/*                                                                                                                  */

/********************************************************************************************************************/

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

1 Trackback or Pingback for this entry