Connecting multiple servers in a single query window using SQLCMD in sql server

With the help of SQLCMD we can connect the multiple servers in a single sql query window. First we need to enable the SQLCMD.

Enabling SQLCMD Mode

For Enabling the SQLCMD Mode we need to Select Query à SQLCMD. See below.

  

Click ok. Now SQLCMD is enabled.

All SQLCMD commands start with a colon, and the command to change servers is ‘CONNECT’. To change to the local server using local host use the following command in a database query window 

:CONNECT BAGESH-PC

Basically it will connect the server and then disconnect that server. Now I am executing the script. Let’s see the result.

  

If we write any sql script it will connect to that server and get faired.

Let’s see I am connecting more than one server in a single window.

:CONNECT BAGESH-PC SELECT [Ename],[Eadd]   FROM [TestSSIS].[dbo].[Emp] :CONNECT BAGESH-PC\SQLSERVER2012 SELECT [Ename],[Eadd]   FROM [TestSSIS].[dbo].[Emp]

Both databases are on different servers.