Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

SQL Server Management Studio SQLCMD mode option


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Management Studio

Problem

I recall in SQL Server 2005 Management Studio (SSMS) that I could quickly go into SQLCMD mode using a button on the SQL Editor toolbar. What happened to it in SSMS after SQL Server 2005, I can't find it?

Solution

I don't really know what Microsoft's reason for this was, but after SSMS 2005 the shortcut has been removed from the toolbar, by default. SQLCMD mode in SSMS allows you to use the command line tool within SSMS. If you are not familar with SQLCMD you can check out information on it here and here. You can enable SQLCMD mode permantenly through SSMS options or enable as you want to with the toolbar button, which is what I prefer to do.

This is the button I am referring to in SSMS 2005:

ssms 2005 sql editor toolbar

I have gone through a basic installation of SQL Server 2008, SQL Server 2008 R2, and even the CTP1 release of SQL Server 2012. All of the versions of SSMS that come with these editions do not have the SQLCMD mode button on the toolbar by default. So, in the remainder of this tip I am just going to provide a screen-by-screen walk through on how to add the button back to the toolbar. SQL Server 2008 and SQL Server 2008 R2 use the same method, but SQL Server 2012 interface for actually adding the button is a little different, so I will seperate that process. Although please note that 2012 I used is just a CPT1 release so it is subject to change by the time we get to the RTM release.

In all the Editions you will first need to open up the customize property window for the toolbars. The quickest way to get to this window is to just click on the Tools menu and select Customize. When that window opens you will click on the "Commands" tab. You will see this window in SQL Server 2008 and 2008 R2:

adding  the sqlcmd mode button on the toolbar by default

Click on "Query" in the left pane, and then go to the bottom of the right pane window. You should see SQLCMD Mode listed as this screenshot shows:

adding the button on sql server 2008 and sql server 2008 r2

Simply left-click on SQLCMD Mode and drag it up to the toolbar. You should see the mouse pointer change when you begin to drag it and once you get it to the toolbar will see something similar to the screenshot below (within the red box that I drew around it). Once you place it where you want it just release it and it will stay in that position.

left click on sqlcmd mode and drag to toolbar

Now when you are in the SSMS version for SQL Server 2012 your Customize "Commands" toolbar window will look similar to this:

in ssms version for sql server denali

At this screen you will select the Toolbar radio button and then select "SQL Editor" in the drop-down menu:

select sql editor in the drop down menu

Then to add the button to this toolbar click "Add Command" button. The next screen is similar to the previous steps where you select Query in the left pane, scroll to the bottom of right pane and select "SQLCMD Mode". Click OK to add it to the toolbar:

select sqlcmd mode and click ok

You can then click on the "Move Down" button to position the shortcut on the toolbar as you desire. As you click you should see the shortcut moving along the toolbar.

Next Steps

Be sure to check out other great tips that are available on MSSQLTips.com. The best way to do this is to become a free member and receive the daily newsletter that has all the latest tips.



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Shawn Melton Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 18, 2015 - 2:19:29 PM - Shawn Melton Back To Top

@Cesar,

It is not gone in SSMS 2012. Still under "Options > Query Execution > SQL Server", listed at bottom of that window as "By default, open new queries in SQLCMD mode".


Thursday, September 17, 2015 - 9:21:59 PM - cesarf Back To Top

thx Shawn,

I've a related issue, it seems ssms 2012 lost open-in-sqlcmd-mode-by-default config option.
do you know why?

br,

cesar.


Thursday, April 09, 2015 - 12:49:48 AM - Jagdeep Sanngwan Back To Top

Thanks for this nice post... it was what i was looking for...

Regards,

Jagdeep Sangwan


Wednesday, January 23, 2013 - 10:11:33 AM - Joseph Back To Top

Thanks, Shawn...Very timely.

2012's AO/HADR generates scripts that must be ran in SQLCMD mode...It would have been nice to see the shortcut back on the 'bar.


Learn more about SQL Server tools