使用链接服务器返回错误-“无法获取OLE DB提供程序的架构行集"； [英] Using linked server returns error - "Cannot obtain the schema rowset for OLE DB provider"

问题描述

如上一个问题所述，我试图将ETL数据从一台sql服务器移动到另一台sql服务器-

解决方案

如果您写的简单选择不起作用，则问题出在链接服务器的安全配置和用户在那里获得的权限.

其中，在执行查询时，无需为源和目标这两个部分都指定服务器名称和数据库名称.您只需要在目标服务器和目标数据库上执行查询.在这种情况下，您的查询将代替

  INSERT INTO [Target_server].[Target_DB1].[dbo].[Target_Table1](Target_Column1)SELECT Source_Column222从[Source_server].[Source_DB1].[dbo].[Source_Table1]WHERE Source_Column1 ='ID7162' 

将如下所示:

  INSERT INTO [dbo].[Target_Table1](Target_Column1)SELECT Source_Column222从[Source_server].[Source_DB1].[dbo].[Source_Table1]WHERE Source_Column1 ='ID7162' 

，您需要在服务器 [Target_server] 和数据库 [Target_DB1] 上打开连接.此外，还需要对照 [Source_server] 在 [Target_server] 上检查链接服务器的安全属性.>

I tried to move data aka ETL from one sql server to another as mentioned in a previous question - Copy data from one column into another column. Now, I get an error when I try to execute a query.

Query -

INSERT INTO [Target_server].[Target_DB1].[dbo].[Target_Table1](Target_Column1)
SELECT Source_Column222
FROM [Source_server].[Source_DB1].[dbo].[Source_Table1]
WHERE Source_Column1 = 'ID7162'

Error -

OLE DB provider "SQLNCLI" for linked server "MYLINKEDSERVER" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "MYLINKEDSERVER" 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 "SQLNCLI" for linked server "MYLINKEDSERVER". The provider supports the interface, but returns a failure code when it is used.

解决方案

If your wrote that simple select is not working, the issue is in security configuration of the linked server and permissions which your user receive there.

Among this, when you execution your query, you don't need to specify the server name and DB name for both parts - source and target. You simply need to execute the query on target server and target database. In this case your query instead of

INSERT INTO [Target_server].[Target_DB1].[dbo].[Target_Table1](Target_Column1)
SELECT Source_Column222
FROM [Source_server].[Source_DB1].[dbo].[Source_Table1]
WHERE Source_Column1 = 'ID7162'

will looks like the following:

INSERT INTO [dbo].[Target_Table1](Target_Column1)
SELECT Source_Column222
FROM [Source_server].[Source_DB1].[dbo].[Source_Table1]
WHERE Source_Column1 = 'ID7162'

and you need your connection to be opened on server [Target_server] and database [Target_DB1]. Also the linked server security properties need to be checked on [Target_server] against the [Source_server].

这篇关于使用链接服务器返回错误-“无法获取OLE DB提供程序的架构行集"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！