[SQL Server] Use "USE Command" with OPENQUERY to Linked Server

[SQL Server] Use "USE Command" with OPENQUERY to Linked Server

Sometimes, you have to use "USE Command" with OPENQUERY to Linked Server.

Maybe you already tried this.

SELECT * FROM OPENQUERY
 (LinkedServer]
 ,'USE AdventureWorks2016;
 EXEC dbo.usp_test1;')
;

But it does not work.
You see the following error message.

Msg 7357, Level 16, State 1, Line 12

Cannot process the object "USE AdventureWorks2016; EXEC dbo.usp_test1;". The OLE DB provider "SQLNCLI11" for linked server

 "LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

You can use "USE Command" by wrapping with "EXEC" command like this.

SELECT * FROM OPENQUERY
       ([LinkedServer],'
       EXEC (
             ''USE AdventureWorks2016;
             EXEC dbo.usp_test1;''
             );'
       )
;

This will work.