Search code examples
sql-server-2008-r2sql-server-2016linked-serveropenquery

SQL Server OPENQUERY running against master database

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

Solution

  • You could use three-part name:

    SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM MyDB.schema_name.Table')