Linked SQL Server - Pass a use command or define default database-CodePudding

I have a set of SQL queries that need to be run against 3 databases, they are stored on 2 different servers that have been linked. All 3 databases have the the exact same tables and tables name, however the database names are different. The queries have been written and all use just table names not the full database.dbo.table syntax so I can't use an automated find and replace on "linked.database1" to "linked.database2" All the queries will always be executed through one server "Server1"

I'm trying to come up with a method of passing the queries to one of the linked servers but have a "use" command go through as it means I only have to change the database in the "use" line.

So far I have tried the following from Server1:

Select * from [Linked].Database.DBO.Table - Successful
Exec ('Select * from [Linked].Database.DBO.Table') - Successful

Direct on Server2 (using the account the linked server operates on):

EXEC ('Use Database; SELECT * from Table') - Successful

So with these above three I can ascertain

The login on Server1 can reach the databases needed and can access the data with a basic select and when passed with and Exec command.
The account on Server2 can run the Exec command and passing the "Use Database" line with Exec works on Server2.

However when I try using the below:

Select * from openquery ([Linked], 'Exec (Use Database; Select * from Table)')

I get the following error:

OLE DB provider "SQLNCLI10" for linked server "172.20.11.123" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 7
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Use'.

Have I messed up the syntax anywhere in the OpenQuery or can the USE command never be passed through OpenQuery. If what I'm trying to do with OpenQuery and USE isn't possible then is there a way of passing the database you want the OpenQuery run against in the statement?

Or as a second thought, is there a way of putting together the Linked Server account so that it uses a particular database as its default database?

CodePudding user response：

No, the way to do this, as far as I can tell, if you know which database you need to execute against:

DECLARE @database sysname = N'Database1';

DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.Table;';

DECLARE @exec nvarchar(4000) = N'[Linked].' 
     QUOTENAME(@database)   N'.sys.sp_executesql';

EXEC @exec @sql;

I'm not quite sure what this was trying to accomplish:

Select * from openquery ([Linked], 
  'Exec (Use Database; Select * from Table)')

But if you take out from that what you're trying to tell [Linked] to EXEC, it's this:

Exec (Use Database; Select * from Table)

Try that anywhere. Broken. You might get it to work like this, as @AlwaysLearning suggested:

Select * from openquery ([Linked], 
  'Exec (''Use Database; Select * from Table'')')

...but that's kind of, like, really gross, compared to the solution at the top. Unless you absolutely need to select from openquery for some reason.