Connecting to multiple servers in a Query Window using SQLCMD

Category : Blogs Published : January 15, 2014 User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
By switching on SQLCMD mode in SQL Server Management Studio you can add some useful extra scripting functionality that is not available with T-SQL. One particularly useful feature is the ability to switch between different SQL Servers within a query window. Without SQLCMD the usual way to do this is to bring up the ‘Connect to Database Engine’ window (for instance by right clicking and selecting ‘Change Connection’). This can become quite tedious, especially when there are several SQL Servers to connect to. Once SQLCMD is enabled this can be scripted within the query window.

Enabling SQLCMD Mode

To enable SQLCMD mode (it is off by default) simply select ‘SQLCMD Mode’ from the Query menu in SSMS, as below : Switching on SQLCMD mode using a menu option

Using SQLCMD to Switch servers in Management Studio

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

:CONNECT localhost

Note that if SSMS intellisense does not shade the background to be grey then the chances are that you’ve not switched on SQLCMD mode.

Alternatively to connect to a specific server (in this case SQLSERVER1) use :

:CONNECT SQLSERVER1

Or for a non-default instance (in this case INSTANCE1 on server SQLSERVER1) :

:CONNECT SQLSERVER1\INSTANCE1

The above commands assume that you are using Windows Authentication, if you want to use SQL Server Authentication and specify a login and password, then you can use the following syntax :

:CONNECT SQLSERVER1 -U MyLoginName -P MyPassword

A Practical Example

By way of example the above technique can be used to get a list of all databases on each SQL Server instance. In my case I have three SQL servers and these are named SQLMATTERS1, SQLMATTERS2 and SQLMATTERS3. I want to connect to each server using Windows Authentication and run the following query :
SELECT * FROM sys.databases
I can do this in SQL Server Management Studio using the techniques described above. The SQL and results are shown in the screenshot below, which is hopefully self explanatory : Switching between servers in SSMS using SQLCMD mode

A Final Note - Using the ‘GO’ Batch Separator

When connecting to multiple servers it’s important to terminate each set of statements to be run with a ‘GO’ batch terminator before switching server with the CONNECT statement. If you don’t then the query may not be run against the correct server.


Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Changing the SQL Server connection within an SSMS Query Windows using SQLCMD Mode.aspx

Keywords

SQL, blogs, SSMS, connection, login, user, password


Comments
Post by Rehan on Tue 17 Jun 2014 07:45. Report Inappropriate Post

Nice Post
can you explain how it will result in one ?
Post by Gary on Fri 12 Dec 2014 23:37. Report Inappropriate Post

Brilliant

I have just seen :CONNECT in a Video but had no idea how to enter it

many thanks to whoever wrote this piece

when I switch I also add this to show the Servername


SELECT @@SERVERNAME
GO


its value seems to reflect the switch done by the :CONNECT


Post by AD on Tue 21 Apr 2015 13:28. Report Inappropriate Post

This is what I was searching for; from last few days. It's amazing to see this mode and can be used. Thx.

But is there any specific command to change the Server Connection in normal SSMS window ; like we have USE DB_Name for change DB.
Post by SQL Matters on Tue 21 Apr 2015 15:48. Report Inappropriate Post

Thanks for your comment.

I don't believe that there is another way of change of changing the server from within SSMS, other than by using SQLCMD mode as per this article.

One other solution, which may or may not work for you depending on what you are trying to achieve, is to use a Central Management Server. You can use this to run the same query against multiple SQL servers in SSMS, with the result for all servers displayed in the same query window. There are quite a few limitations though around using CMS, so this may not be of use.
Post by Nilesh on Fri 12 Jun 2015 15:45. Report Inappropriate Post

This great tip helped me!
Many thanks.
Post by l on Tue 17 Nov 2015 09:57. Report Inappropriate Post

Is it possible to obtain all results in one unique table ?
Post by Ravi on Wed 18 Nov 2015 03:48. Report Inappropriate Post

Is it possible to use previous result set output variable in consecutive connections as input parameter?
Post by teamour on Mon 21 Dec 2015 10:57. Report Inappropriate Post

thanks
Post by Shishir on Fri 04 Mar 2016 07:17. Report Inappropriate Post

I have encountered the following error:

Fatal Scripting Error. Cannot open connection specified in the SQLCMD script.

Please advice on further steps.

Thanks


Post by steve on Fri 01 Apr 2016 23:40. Report Inappropriate Post

HUGELY HELPFUL--THANK YOU !!!!!!!!!!!!!!!!
Post by Mohammed on Fri 15 Apr 2016 15:55. Report Inappropriate Post

Hi, I am trying to connect to azure SQL Database. So by default its connecting to Master Database, So can you suggest any tip to how to connect to database as well. So far i was able to connect to Azure server using Login and Password but could not connect to right database instead of master.
Post by Andrew on Tue 06 Sep 2016 11:09. Report Inappropriate Post

This maybe a simple question but when linking servers, and creating views containing data from both servers; which server uses up resources? (Is it which ever server the query is running on, or which ever the data is saved on).
E.g.
I have 1 server (A) which all the data is saved on, but I also have another server (B) which is used for reporting queries - when running queries, which would take the biggest 'hit'?
Post by Oliver on Mon 13 Feb 2017 10:46. Report Inappropriate Post

Brilliant. My first time of hearing/seeing this.

Thanks
Post by Jane on Wed 22 Feb 2017 00:24. Report Inappropriate Post

very useful! I've been searching for a solution for a while!!
Post by Teresa S. on Thu 09 Mar 2017 23:39. Report Inappropriate Post

Is there a way to do this without typing your password into the code?
Post by SQL Matters on Fri 10 Mar 2017 09:25. Report Inappropriate Post

Teresa S - you can use Windows Authentication to avoid putting your login name and password into the code. If you have to use SQL Server Authentication then maybe create a dedicated login with low privileges for this.
Post by Kat on Fri 21 Apr 2017 21:58. Report Inappropriate Post

How to you use the :CONNECT script with windows authentication? I tried the following and it did not work:
:Connect myserver -U mydomain\myname -P mypassword
Post by SQL Matters on Mon 24 Apr 2017 11:29. Report Inappropriate Post

Kat - you can use the following syntax to connect to a server with windows authentication using the currently logged in user (no need to specify login name and password) :

:CONNECT SQLSERVER1

However if you want to connect as another windows user, you'd need to login to windows as that user first (as it's windows that authenticates the user name and password).
Post by Anjum Rizwi on Thu 09 Nov 2017 08:12. Report Inappropriate Post

Your "Related Articles "link not redirecting to any other page.
Post by Galaxiom on Tue 17 Apr 2018 01:48. Report Inappropriate Post

Another alternative to running queries against another server is to link the servers. This allows a single query to be run against resources from multiple servers.

However be aware the that the security setup to pass through Kerberos authentication to the linked server is quite involved so don't try this unless you have some considerable time to implement it. Be sure to completely understand the security implications of passing authentication between the machines and configure it properly to avoid opening up more than intended.
Post by Ghouse Mohammed on Mon 14 Jan 2019 16:11. Report Inappropriate Post

I Want to run a stored procedure across multiple servers and when i supply a parameter with the Server name in it through a cursor to :Connect @name

It gives me an error.

Is there a way i can loop through server names and connect to them individually to run a query?

Thanks.
Post by William McKelvey on Wed 13 Mar 2019 15:23. Report Inappropriate Post
Website : https://www.pmpa.com
I am also trying to use a variable to hold the target server name and it appears to be trying to execute before the variable has a value assigned. I time out with server not found or network error.
Post by DVP on Fri 22 Mar 2019 23:05. Report Inappropriate Post

Is there a way I can run a batch script in a bat file with multiple sql files in SQLCMD mode ?
This is to automate a restore of few Databases and then add them back to a SQL HAG Group .

Now I manually paste teh sql files in SSMS and enable SQLCMD mode and run them in a seaprate window fo re each db . very manual.
Post by n0b0n on Sat 20 Jul 2019 07:34. Report Inappropriate Post

GREAT SYNTAX..!!!
it's very help full, tanks a lot
Post by surya on Tue 26 Nov 2019 08:43. Report Inappropriate Post

how to use insert data query to insert data on table between two different servers
Post by David on Wed 03 Jun 2020 09:26. Report Inappropriate Post

Hi,

I am connecting to multiple servers, but I can not connect to all of them, becouse some of them are canceled. Is there a way how to automaticly skip these servers and continue with another one? In this moment, it will stuck on the one it can not connect with and the error appears.

Thank you
Post by Eske Rahn on Fri 31 Jul 2020 09:23. Report Inappropriate Post
Website : https://eskerahn.dk
A late reply @Ghouse Mohammed , I would suggest to simply make a script that through the loop had the script to run as output, and then run that. (Obviously you could save the output script too, if your server-park are not changing often)
Post by PV on Tue 05 Jan 2021 17:02. Report Inappropriate Post

How to disconnect explicitly?
Post by Eske Rahn on Tue 05 Jan 2021 17:35. Report Inappropriate Post
Website : https://eskerahn.dk
@PV AFAIK it disconnects automatically at the end (and at each shift), se e.g. this
https://www.mssqltips.com/sqlservertip/2311/using-the-ssms-query-editor-in-sqlcmd-mode/
Post by Nush on Fri 05 Feb 2021 05:56. Report Inappropriate Post

Hi,

Great article!

Can you please help me with below code? I need to pass the server name as a parameter.

declare @BackupServer varchar(50) = 'MyBackupServer'

:setvar SRC @BackupServer
:CONNECT $(SRC)
SELECT @@Servername as SourceServer

TIA
Post by Mr T on Mon 08 Feb 2021 16:40. Report Inappropriate Post

Please remove the lines containing the local variable @BackupServer in your example and replace it by :setvar SRC "localhost" where localhost is the example value.
If the value needs to be set outside the script: make sure an OS environment variable is set beforehand (like SET SRC=localhost) and leave the :setvar out then.
Post by Flemming on Fri 17 Dec 2021 06:50. Report Inappropriate Post

is it possible to make joins in SSMS with :connect (where linked servers is not allowed ) ?

something like

:Connect DBserver1
use DB1
select * from table1 inner join
(:Connect DBserver2
use DB2
select * from table2) as table2_db2

on

table1.id = table2_Db2.id


Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered