I am trying to select from a table that is stored on a different server, I am using opendatasource. When I run the query, I get:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "(null)" does not contain the table ""TESTTBL"."dbo"."TBLNAME"". The table either does not exist or the current user does not have permissions on that table.
The select statement is as following:
'Data Source=DESTSERVR\INSTANCE;Integrated Security=SSPI').[TESTTBL].dbo.[TBLNAME]
Both database and table do exist on the destination server, I tested the same query from another server and it works. I did query all environments sysservers, and they are all setup identical, please advice.