Execute a query from a SPECIFIC Server/DB

Dom 241 Reputation points
2023-03-17T17:12:52.6733333+00:00

I will often be working with queries with multiple servers and databases open in SSMS at the same time. When I run a query, I know I can use [DATABASE] to run in a specific, but SS seems to get confused about which SERVER I want to run on. I'm logged into several different servers in SSMS (all with different credentials). How can I FORCE my query to execute ON SERVER-A using DB-B?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,070 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 31,096 Reputation points
    2023-03-17T19:51:51.2933333+00:00

    query windows are tied to server connection. you can change the database (use database) from the drop down. to change the connection, in the query window right click and pick:

    connection->change connection


  2. CosmogHong-MSFT 8,526 Reputation points Microsoft Vendor
    2023-03-20T02:46:31.7966667+00:00

    Hi @Dom

    You could go to SQLCMD mode in an SSMS query Window (menu bar Query-->SQLCMD mode) using SQL command :CONNECT to connect other server and USE to specify database.

    User's image

    Check this example:

    :CONNECT Server1  
    Use Database_A  
    Select * from Table1
    GO  
      
    :CONNECT Server2  
    Use Database_B  
    Select * from Table2 
    GO  
      
    :CONNECT Server3  
    Use Database_C  
    Select * from Table3 
    GO  
    

    Note that the query window connection reverts back to the initial connection after each GO.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.