:CONNECT in SSMS
7March 13, 2014 by Kenneth Fisher
Those people who are familiar with SQLCMD will recognize this command. It is used to connect to an instance from within a SQLCMD script. What they may not realize is that this command (and other SQLCMD commands) can be used in a query window by turning on SQLCMD mode. There is a great overview of using SQLCMD commands in SSMS here.
I am going to highlight an interesting aspect of the CONNECT command in SSMS.
First a basic example. Note: all of the connections start on the instance (local)\SQL2012
SELECT @@SERVERNAME GO :CONNECT (local)\SQL2008R2 SELECT @@SERVERNAME GO :CONNECT (local)\SQL2012 SELECT @@SERVERNAME GO
With an output of
So using :CONNECT you can change connections inside a script. I’ll frequently use this technique to get a piece of information from a number of different servers at once.
For example, how many databases do I have on each server?
:CONNECT (local)\SQL2012 SELECT @@SERVERNAME, COUNT(1) FROM sys.databases GO :CONNECT (local)\SQL2008R2 SELECT @@SERVERNAME, COUNT(1) FROM sys.databases GO
There are a couple of odd aspects to the CONNECT command. The connect command happens at the beginning of the batch regardless of where in the batch it is, and if you have more than one of them in a single batch only the last one counts. I did have a couple of runs where I got different results but I couldn’t reproduce them and they only happened once or twice so it may have been a PEBCAK issue. Again remember that my connections are all initially on (local)\SQL2012.
SELECT @@SERVERNAME :CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME :CONNECT (local)\SQL2008R2 SELECT @@SERVERNAME :CONNECT (local)\SQL2012 SELECT @@SERVERNAME GO :CONNECT (local)\SQL2012 SELECT @@SERVERNAME :CONNECT (local)\SQL2008R2 SELECT @@SERVERNAME GO
SQL Studies 
I’ve seen it behave wierdly a couple times too, but never quite figured out why. It made me think that the best approach is to make sure the query is initially disconnected. I tend to hold connections open and if the connect goes strange, it could run on the wrong instance. Or maybe I was just tired!
The weird behavior does worry me sometimes but I’ve never had a problem as long as I only have one :CONNECT in a batch. The first time I tested this out and got unusual results I honestly thought I was just seeing things. Tried it again and then I knew I was. Until it happened again a while later.
Great post– I’m a huge fan of SQLCMD in SSMS to simplify some tasks. It can be tricksy though: I love your example with the batches and agree that it’s worth adding in validation/error handling to make sure you’re really connected to the place you think you are!
Thanks! I appreciate the comment. :CONNECT in general has given me some weird results. It tends to make me more cautious. I still use it for a quickie check of multiple instances though.
I’ve learned the most about :CONNECT when I used the ‘New Availability Group Wizard’ to configure my AG, then generated the script right before executing. Loads of :CONNECT statements there, and it *seems* like the GO statement breaks up the connections between the various instances in the AG. Like you, Kendra and Kenneth, I’ve seen strangeness when I try to hand-code multiple :CONNECT statements and am a bit leery about running them.
Why you use COUNT(1) instead of COUNT(*) ? Does it have any performance-wise justification?
Nope no difference. It’s as much habit as anything. Here is a link you can go to with more detail and some other interesting links. http://stackoverflow.com/questions/1221559/count-vs-count1
Just for fun though try this some time:
Also be aware that COUNT(columnName) is different. For example it won’t count null values.