0

I have the following statement

SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM Table')

Which is failing with the error

OLEDB provider "SQLNCLI10" for linked server "MyLinkedServer" returned messages "Deffered prepare could not be completed"

When I try

SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT DB_NAME()')

I can see that the statements are being run against the master database on the linked server.

I have tried

SELECT * FROM OPENQUERY(MyLinkedServer,'USE MyDB; SELECT * FROM Table')

But that also tells me the statement could not be prepared.

How do I control which database on the linked server the OPENQUERY runs against?

The version of SQL server I am running the query on is 2008R2 and the remote server is 2016SP1

SE1986
  • 2,534
  • 1
  • 10
  • 29
  • If you are only ever going to use one DB on the remote server, you can set the catalog (Default DB to use) on the Linked server setup. Otherwise see https://stackoverflow.com/questions/495719/querying-a-linked-sql-server – DancingFool Apr 06 '18 at 00:39

1 Answers1

2

You could use three-part name:

SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM MyDB.schema_name.Table')
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275