Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I execute a simple query on the linked server that uses a User that is mapped across the two servers this errors. I tried Test Link Server connection which works ok.

What is confusing is that something I stumbled across on the WEB was to try using OPENQUERY which did work.

the following errors returned when I executed my query not using the OPENQUERY method.

OLE DB provider "SQLNCLI10" for linked server "xxx.xxx.x.x" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "xxx.xxx.x.x" 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 2 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "192.168.9.5". The provider supports the interface, but returns a failure code when it is used.

Many thanks

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

It's trying to run the following stored procedure:

exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL

...but it does not exist on the 32 bit version. You can create a wrapper with that sp name which calls the sp_tables_info_rowset 32 bit version.

I believe this is fixed in a service pack on 2000 and 2005 so apply that if possible.

share|improve this answer
add comment

The following script is the one I used to create the needed stored procedure sp_tables_info_rowset_64 that's a wrapper around sp_tables_info_rowset and it fixed the same error for me. Run it on the SQL Server 2000 box.

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

grant exec on sp_tables_info_rowset_64 to public
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.