SQL Server 2005 x64 利用linked server方式連結 SQL Server 7 32bit

JDP

Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?
Filed under: Uncategorized — jmansford @ 14:10


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   <br />    @server=‘MySQLServer7′
Then I try
SELECT * FROM [MySQLServer7].[msdb].[dbo].[sysfiles] – Or any other SQLI 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 ... =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<br />       @table_name sysname, <br />       @table_schema     sysname = null,   <br />       @table_type nvarchar(255) = null <br /> as<br /> declare @Result int set @Result = 0<br /> exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
This works a treat - cheers Marek!

Reference: http://joelmansford.wordpress.co ... as-a-linked-server/

JDP

經過測試得知，只需要在SQL Server 7.0上，執行SQL Server 2000 SP4裡面附的instcat.sql，即可解決此問題！