THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Maria Zakourdaev

SQLCMD Mode: give it one more chance

 

- Click on me. Choose me. - asked one forgotten feature when some bored DBA was purposelessly wondering through the Management Studio menu at the end of her long and busy working day.

- Why would I use you? I have heard of no one who does. What are you for? - perplexedly wondered aged and wise DBA. At least that DBA thought she was aged and wise though each day tried to prove to her that she wasn't.

- I know you. You are quite lazy. Why would you do additional clicks to move from window to window? From Tool to tool ? This is irritating, isn't it? I can run windows system commands, sql statements and much more from the same script, from the same query window!

- I have all my tools that I‘m used to, I have Management Studio, Cmd, Powershell. They can do anything for me. I don’t need additional tools.

- I promise you, you will like me. – the thing continued to whine .

- All right, show me. – she gave up. It’s always this way, she thought sadly, - easier to agree than to explain why you don’t want.

- Enable me and then think about anything that you always couldn’t do through the management studio and had to use other tools.

- Ok. Google for me the list of greatest features of SQL SERVER 2012.

- Well... I’m not sure... Think about something else.

- Ok, here is something easy for you. I want to check if file folder exists or if file is there. Though, I can easily do this using xp_cmdshell …

- This is easy for me. – rejoiced the feature.

By the way, having the items of the menu talking to you usually means you should stop working and go home. Or drink coffee. Or both. Well, aged and wise dba wasn’t thinking about the weirdness of the situation at that moment.

- After enabling me, – said unfairly forgotten feature (it was thinking of itself in such manner) – after enabling me you can use all command line commands in the same management studio query window by adding two exclamation marks !! at the beginning of the script line to denote that you want to use cmd command:

image

-Just keep in mind that when using this feature, you are actually running the commands ON YOUR computer and not on SQL server that query window is connected to. This is main difference from using xp_cmdshell which is executing commands on sql server itself. Bottomline, use UNC path instead of local path.

- Look, there are much more than that. - The SQLCMD feature was getting exited.- You can get IP of your servers, create, rename and drop folders. You can see the contents of any file anywhere and even start different tools from the same query window:

image

Not so aged and wise DBA was getting interested: - I also want to run different scripts on different servers without changing connection of the query window.

- Sure, sure! Another great feature that CMDmode is providing us with and giving more power to querying. Use “:” to use additional features, like :connect that allows you to change connection:

image

- Now imagine, you have one script where you have all your changes, like creating staging table on the DWH staging server, adding fact table to DWH itself and updating stored procedures in the server where reporting database is located.

- Now, give me more challenges!

- Script out a list of stored procedures into the text files.

- You can do it easily by using command :out which will write the query results into the specified text file. The output can be the code of the stored procedure or any data. Actually this is the same as changing the query output into the file instead of the grid.

image

- Now, take all of the scripts and run all of them, one by one, on the different server. 

- Easily

- Come on... I’m sure that you can not...

-Why not? Naturally, I can do it using :r commant which is opening a script and executing it. Look, I can also use :setvar command to define an environment variable in SQLCMD mode. Just note that you have to leave the empty string between :r commands, otherwise it’s not working although I have no idea why.

image

- Wow.- She was really impressed. - Ok, I’ll go to try all those…

-Wait, wait! I know how to google the SQL SERVER features for you! This example will open chrome explorer with search results for the “SQL server 2012 top features” ( change the path to suit your PC):

image

“Well, this can be probably useful stuff, maybe this feature is really unfairly forgotten”, thought the DBA while going through the dark empty parking lot to her lonely car. “As someone really wise once said: “It is what we think we know that keeps us from learning. Learn, unlearn and relearn”.

Published Friday, May 11, 2012 5:26 PM by Maria Zakourdaev
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tony said:

I love SQLCMD mode. We use it extensively where I work, for build scripts. I don't know why people don't like it.

May 12, 2012 12:35 PM
 

Joe Moyle said:

When SQL 2005 came out SQLCMD became my favorite feature.  It allowed me to start doing build scripts more like I did in Oracle.  I never knew about the !! until today though so now SQLCMD gives me one more tool like I use with SQL*Plus in Oracle.  Thanks for this article.

May 12, 2012 2:13 PM
 

Tal said:

Great post Maria!!

C.U.

-Tal.

May 12, 2012 3:32 PM
 

Lit said:

Thanks for this usefull info!

May 14, 2012 3:25 PM
 

jeff_yao said:

I use CMD mode all the time, and the best thing is that you can run the same script(s) to different servers from a same SSMS query window.

May 14, 2012 5:41 PM
 

IowaTechBear said:

I saw Kimberly Tripp use SQLCMD a couple of months ago at an Immersion Event in Tampa, but that was my first exposure.  I liked what I saw but I was not sure how to really use it or just what it could do.  Thank you for opening that door for me. I have now become a fan of this "Forgotten Feature."

May 18, 2012 11:16 AM
 

Maria Zakourdaev said:

Thank you all for the feedback! Great to hear that this feature is not as overlooked as it thought of itself.

May 20, 2012 11:48 AM
 

sudi said:

and she went ahead and wrote a very readable article on it!

thanks Maria, for this very useful article.

Question: is there anyway one could "change directory" to a specific folder say c:\sudi\mycode\sql\QA3, So that the subsequent !!COMMANDs use that folder as the "current folder" ?

thanks in advance !

September 27, 2013 5:53 AM
 

Maria Zakourdaev said:

Sudi, I don't think you can. Instead, you can define environment variable :setvar CurrentFolder

October 22, 2013 7:57 AM
 

anonymous said:

Another link to a good read on SQLcmd on what can, and cannot be done:

http://blogs.msdn.com/b/sql_server_appendix_z/archive/2013/02/23/sql-server-management-studio-sqlcmd-meets-the-richter-scale.aspx?CommentPosted=true#commentmessage

Thank you for sharing your observations. It's cool to be able to run Windows commands from SQL

November 1, 2013 3:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Maria Zakourdaev

The shortest word in the English language that contains the letters: abcdef is… feedback! Let me know if I have touched something that is alive in the cosmos.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement