Archive for the ‘Linked Server’ Category

How to resolve the 64-bit to 32-bit linked server error?

You have been requested by the Development team to create Linked Servers from your reporting server to 3 other SQL Servers instances for data access.  You have completed the request in a minute or less.  When you attempted to query the tables on the Linked Servers that you had created you received the following error:

OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME” 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 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server “SERVERNAME “. The provider supports the interface, but returns a failure code when it is used.

After further review, you have found that your reporting server is running SQL Server 2008 R2 Enterprise Edition 64-bit and the 3 SQL Server instances are running SQL Server 2000 Enterprise Edition 32-bit.

What can you do to resolve this problem to enable the Development team to use the Linked Servers as soon as you can?

First, you need to create the sp_tables_info_rowset_64 stored proc:

-- Create the sp_tables_info_rowset_64 stored proc in the master database
USE master
go
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

Then, you need to grant the execute permission on the stored proc created in the above step:

-- Grant the EXEC on the stored to the principal
USE master
go
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC -- replace PUBLIC with security account for the linked server

REFERENCES:

Issue with 64-bit SQL Server using SQL 2000 linked server
http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx