Connecting to multiple servers in a Query Window using SQLCMD

Category : Blogs Published : January 15, 2014 User Rating : 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 : http://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 a comment   No login required !

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