Run Query with Different Server and Different Database

Question

Hi - How I can run the following same query in different servers and database. How we can we use the 'USE' command with Server to mover server.

Use C_Server1
Use Per_A
Select * from YTR where surname like '%Test%'

Use C_Server2
Use Cer_A
Select * from YTR where surname like '%Test%'

Use C_Server3
Use Uer_F
Select * from YTR where surname like '%Test%'

Answer 1

USE switches database context on the current connection. You can use SQLCMD mode in an SSMS query Window (menu bar Query-->SQLCMD mode) in order to use SQLCMD commands like :CONNECT in the example below. Note the the query window connection reverts back to the initial connection after each.

:CONNECT C_Server1  
Use Per_A  
Select * from YTR where surname like '%Test%'  
GO  
  
:CONNECT C_Server2  
Use Cer_A  
Select * from YTR where surname like '%Test%'  
GO  
  
:CONNECT C_Server3  
Use Uer_F  
Select * from YTR where surname like '%Test%'  
GO  

Answer 2

Hi @Mike ,

You could also have a try with Linked Servers.

Linked servers offer the following advantages:

• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

You could get to them in SSMS from the following location in the tree of the Object Explorer:

Server Objects-->Linked Servers

or you can use sp_addlinkedserver.

Then you could query as follows:

select * from LinkedServerName.DatabaseName.OwnerName.TableName  

After you created two linked servers in C_Server1, then you could use below query:

Use C_Server1  
go  
  
select * from [Per_A].[dbo].[YTR] where surname like '%Test%'  
go  
  
select * from [C_Server2].[Cer_A].[dbo].[YTR] where surname like '%Test%'  
go  
  
select * from [C_Server3].[Uer_F].[dbo].[YTR] where surname like '%Test%'  
go  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The USE command only works for moving between databases within the same server instance.
It is not possible to move to another server by means of a command because the other server will need another connection. Your existing connection, the one that you are using for sending the commands, is connected to one specific server. You cannot send a command to that server to open the connection to a different server, because the connection has to be opened by the client application that you are using for sending the commands, not by the target server that is currently receiving the commands.