Learn more about SQL Server tools

   
   















































Using the SSMS Query Editor in SQLCMD Mode

MSSQLTips author Diana Moldovan By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | More > 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".

enable the sqlcmd mode of query editor

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:

run this script in the query editor and 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.

change the the results to text in sql server

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


Last Update: 2/24/2011


About the author
MSSQLTips author Diana Moldovan
Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, December 04, 2014 - 12:35:56 AM - soumya Read The Tip

 

Hi Diana

GM.

 

I have a Stored procedure with multiple queries(select statements with some conditions in each query).

I want to export the data of each of these queries automatically to an excel output with multiple tabs in it.

Can you please suggest me some method for the same. I use sql server management studi 2008.

i tried to export one query to one .csv file, though not to excel file.

Let me know if you need any further information.

 

Thanks

Soumya


Friday, February 25, 2011 - 5:49:41 AM - Diana Moldovan Read The Tip

@Jay - This is a useful and much underappreciated feature of the Query Editor.
I should have insisted more on the fact that you can run a different script against each server you connect to.


Thursday, February 24, 2011 - 12:17:43 PM - Jimmy May Read The Tip

Thursday, February 24, 2011 - 11:39:33 AM - Jay Read The Tip

I've used SQLCMD for a while now to develop upgrade scripts.  Throughout the development lifecycle, each 'change' is scripted to a file.  For safety, each change script has the following at the top of the script:

 

USE [$(_database)];

GO

...

 

Then I have a _deploy.sql script that lists each change script like the following:

 

:r $(ScriptPath)\sprint4.1\26.0.sql

GO

 

:r $(ScriptPath)\sprint4.1\27.0.sql

GO

 

 

Then, I have a _deployMaster.sql that defines the database connection and then calls _deploy.sql which calls each change script in order.  This makes deployments easy when you are dealing with 10 to 20 scripts that have to be executed in order.  Also, I can easily change connections between which server/database I want to run the scripts against without worrying about accidently running one of the scripts on the wrong database.  Throw in snapshots and you can 'practice' your upgrades over and over making sure it will go without a hitch.   _deployMaster.sql example is below:

 

 

:setvar ScriptPath "E:\tegron\PlantWorkFlowBranch\AKR80731E_DuolineGilmerInstall\database\upgrades"

:setvar _server "(local)\sql2005"

:setvar _user "sa"

:setvar _password "xxx"

:setvar _database "database"

:setvar _databaseSnapshot ""

 

:connect $(_server) -U $(_user) -P $(_password)

 

RAISERROR('  Server: %30s',0,1,@dbServer);

RAISERROR('Database: %30s',0,1,@dbName);

RAISERROR('    Time: %30s',0,1,@getDate);

RAISERROR('    User: %30s',0,1,@sUserName);

RAISERROR('  Client: %30s',0,1,@hostName);

 

PRINT char(13) + char(10)

PRINT '================='

PRINT 'Deploy Sprint 4.0'

PRINT '================='

:r $(ScriptPath)\sprint4.0\_deploy.sql

GO

 

PRINT char(13) + char(10)

PRINT '================='

PRINT 'Deploy Sprint 4.1'

PRINT '================='

:r $(ScriptPath)\sprint4.1\_deploy.sql

GO

 

 

 

If you scatter print statements throughout your scripts, then you will end up with a nice log of your upgrade.




 
Sponsor Information