SQL 2008 LINKSERVER SQL 2000时执行查询出现的错误 DBSCHEMA_TABLES_INFO

今天在在SQL 2008的服务器上建了link server，此link server是SQL 2000。。

错误信息如下：

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

OLE DB provider "SQLNCLI10" for linked server "XXXXX" 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 "XXXXX". The provider supports the interface, but returns a failure code when it is used.

 

在网上查了资料后，将解决方法记录下来。

1、确保SQL 2000安装了SQL SP4补丁。

2、安装完补丁后，在master数据库上执行下面的语句，创建一个sp，就可以了。

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

 

转载于:https://www.cnblogs.com/feiniu/p/Wayne.html