By: Diana Moldovan | Updated: 2011-02-24 | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Management Studio
Problem
The SQL Server Management Studio Query Editor allows editing and running SQLCMD scripts. Although this feature is not very popular, it comes handy when you need to run and/or schedule scripts against multiple servers or databases, or when you need to test scripts that you plan to run using the command line, especially if you manage a small number of servers. This tip will offer you the basics of editing SQLCMD scripts using the Query Editor.
Solution
First of all, you'll need to enable the "SQLCMD mode" of the Query Editor, which is "disabled" by default. To do so, go to the Query menu and click on the "SQLCMD mode" item or press ALT+Q+M.
If you need SQLCMD mode to be the default, go to Tools -> Options -> Query Execution -> SQL Server -> General and check the "By default, open new queries in SQLCMD mode".
Bear in mind that neither intellisense nor debugging are available in SQLCMD mode.
Here is a script which you can use to collect data about the SQL Server versions and editions running on the servers in your network. I've run it in an environment consisting of 3 virtual machines each of them running different editions of MS SQL Server. The machines belong to a Win 2008 R2 domain. I logged in on one of these machines (running SQL 2008 SP1), using a domain account which is in the sysadmin server role and local admin on all the boxes.
:ON ERROR EXIT
:CONNECT (local)
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductLevel
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO
:CONNECT SQL2
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductLevel
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('EngineEdition') AS EngineEdition
GO
Notice the lines beginning with a semicolon - these are SQLCMD commands. Only a subset of the SQLCMD commands (enumerated in BOL) is available for use from within the Query Editor. They should appear on a grey background if the editor is running in SQLCMD mode.
The ":CONNECT" command connects to the server and instance specified - in this case I connect to the default instances of the (local) and "SQL2" servers. A trusted connection will be used by default. To connect using SQL authentication, you'll have to provide environment variables such as the user name and the password.
The ":ON ERROR" sets the action to be performed if the script encounters an error.
Don't forget the GO batch separator. If GO is missing, both SELECT statements will run only against the last server, in this example against SQL2, even if the connection is changed, and there are the "Connecting to (local)..." / "Disconnecting connection from (local)..." / "Connecting to SQL2..." / "Disconnecting connection from SQL2...." messages in the Messages tab. Press "Execute" or F5 to run this script in the Query Editor. The results should look like this:
You may want to capture the errors and the query results in a file. To do this, you have to add a few lines to the script, which will look like this:
:ON ERROR EXIT
:SETVAR Path "C:\MyLocation\"
:SETVAR FileName "QueryResults.txt"
:OUT $(Path)$(FileName)
:SETVAR ErrPath "C:\ErrLogs\"
:SETVAR ErrFileName "Err.txt"
:ERROR $(ErrPath)$(ErrFileName)
:CONNECT (local)
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductLevel
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO
:CONNECT SQL2
SET NOCOUNT ON;
SELECT SERVERPROPERTY('ServerName') AS ServerName
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductLevel
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('EngineEdition') AS EngineEdition
GO
The ":SETVAR" command defines the sqlcmd scripting variables. You'll call these variables later in the script by prefixing them with $ and including them in parenthesis, like this: $(variable_name).
The ":OUT" will redirect the results to an output file.
The ":ERROR" will rediirect the error messages to an output file.
For this script the output file will need some additional formatting work. To make your life a little easier, switch the results to text. Do do this, go to Tools -> Options -> Query Results -> SQL Server -> Results To Text and choose an "Output format" different from "column aligned", for example "comma delimited" as shown below.
The content of my ouput file looks like:
Connecting to (local)... ServerName,ProductVersion,ProductLevel,Edition,EngineEdition DEV2008-2,10.0.2531.0,SP1,Developer Edition,3 Disconnecting connection from (local)... Connecting to SQL2... ServerName,ProductVersion,ProductLevel,Edition,EngineEdition SQL2,10.50.1600.1,RTM,Enterprise Edition (64-bit),3 Disconnecting connection from SQL2...
The files are re-created each time you run the script.
Here is another example in which a .sql script ("revoke.sql") is executed against multiple databases on the local machine. In this example I use 2 databases in which the "guest" user is granted access which I want to revoke.
:ON ERROR EXIT :SETVAR Path "C:\MyLocation\" :SETVAR ScriptToExecute "revoke.sql" :SETVAR ErrPath "C:\ErrLogs\" :SETVAR ErrFileName "Err.txt" :ERROR $(ErrPath)$(ErrFileName) USE TestDB1 :R $(Path)$(ScriptToExecute) USE TestDB2 :R $(Path)$(ScriptToExecute)
The "revoke.sql" script revokes the CONNECT right to the "guest" user. Below is the contents of the "revoke.sql" script.
REVOKE CONNECT FROM GUEST GO
The ":R" parses the .sql file and loads it into the statement cache. It will be executed against the 2 databases specified.
You can easily check the "guest" account status using the advice that K. Brian Kelley gives here: http://www.mssqltips.com/sqlservertip/2038/understanding-how-a-user-gets-database-access-in-sql-server/
You can also execute operating system commands (prefixed with !!) from within the Query Editor in SQLCMD mode. For example try to run this script on your local machine:
EXEC master.dbo.xp_cmdshell systeminfo GO !!systeminfo
The "!!systeminfo" still returns the results, even if the xp_cmdshell is disabled on your box.
Definitely the SQLCMD mode of the Query Editor is not the most user friendly feature. However you may find it useful for some admin tasks, especially if your environment is not very complex.
Next Steps
- BOL offers one of the best resources both for sqlcmd and for using sqlcmd from within the Query Editor. See http://msdn.microsoft.com/en-us/library/ms162773.aspx and http://msdn.microsoft.com/en-us/library/ms174187.aspx
- You can find some good stuff here http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/#eighth - however the macro statements list appears a little bit "skewed" and for this reason difficult to read.
- Don't miss this interesting article about the SQLCMD execution order: http://sqlblog.com/blogs/andrew_kelly/archive/2009/06/05/sqlcmd-execution-order.aspx
- Check this for another way to execute TSQL against multiple instances without CMS: http://www.mssqltips.com/sqlservertip/2086/run-sql-commands-against-multiple-sql-servers-without-cms/
- You may find that sp_MSforeachdb is also useful, see http://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/.
- And you can find a Powershell version of sp_MSforeachdb
About the author
Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-02-24
