Using linked server returns error - "Cannot obtain the schema rowset for OLE DB provider"

Question

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.

Does simple `SELECT` works? Are you sure the account for the linked server authentication has permissions for this given object? — Yosi Dahari, Oct 18 '13 at 23:17
I think this is a DBA question - http://dba.stackexchange.com/ — Yosi Dahari, Oct 18 '13 at 23:23
Only moderators can I think, you can ask for it using the relevant flag — Yosi Dahari, Oct 18 '13 at 23:52

score 0 · Accepted Answer · answered Oct 31 '13 at 12:03

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].

score -1 · Answer 2 · edited May 23 '17 at 10:28

-1

If you are using loop back linked server (linked server refering to the same database in the same server) then refer the below mentioned link:

Transaction with loopback linked server - locking issues

If that is not the case, this is the solution provided by Microsoft.

edited May 23 '17 at 10:28

Community

1
1

answered Feb 27 '16 at 05:00

devdey

24
3

Using linked server returns error - "Cannot obtain the schema rowset for OLE DB provider"

2 Answers2

Linked