Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

:CONNECT in SSMS

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

CONNECT1

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

CONNECT2

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

CONNECT4

SELECT @@SERVERNAME
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME
:CONNECT (local)\SQL2012
SELECT @@SERVERNAME
GO
:CONNECT (local)\SQL2012
SELECT @@SERVERNAME
:CONNECT (local)\SQL2008R2
SELECT @@SERVERNAME
GO

CONNECT3


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, T-SQL Tagged: language sql, microsoft sql server, SSMS

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...