SQLCMD mode:give it one more chance

From:http://sqlblog.com/blogs/maria_zakourdaev/archive/2012/05/11/sqlcmd-mode-give-it-one-more-chance.aspx?utm _source=tuicool

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

-Why would I? I have heard of no one who does. What is your for? -Perplexedly wondered aged and wise DBA. At least that DBA thought she is aged and wise though each day tried to prove to hers that she wasn ' t.

-I know you. You are quite lazy. Why would does additional clicks to move from windows 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 had all my tools that I ' m used to, I had Management Studio, CMD, Powershell. They can do anything for me. I don ' t need additional tools.

-I promise you and you'll like me. , haven thing continued to whine.

-All right, show me. –she gave up. It's always the This is, she thought sadly,-easier to agree than to explain why don't you want.

-Enable me and then think about anything so you always couldn ' t does through the Management Studio and had to use other T Ools.

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

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

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

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

By the the-the-the-the-the-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 on the weirdness of the situation at that moment.

-After enabling Me,–said unfairly forgotten feature (it is thinking of itself in such manner) –after enabling me can use any command line commands in the same Management Studio query window by adding, exclamation marks ! The beginning of the script line to denote so want to use cmd command:

-just Keep in mind if the using this feature is actually running the commands on YOUR computer and not on SQL ser Ver that query window is connected to. This is the main difference from using xp_cmdshell which is executing commands on SQL Server itself. Bottomline, use UNC path instead of local path.

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

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

-Sure, sure! Another great feature that Cmdmode are providing us with and giving more power to querying. Use ': ' to use additional features, like : Connect This allows to the change connection:

-Now imagine, you had one script where you had 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 would write the query results into the specified text file. The output can is the code of the stored procedure or any data. Actually the same as changing the query output into the file instead of the grid.

-Now, take any 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 a environment variable in SQLCMD mode. Just Note that you had to leave the empty string between:r commands, otherwise it's not working although I had no idea Why.

-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 would open Chrome Explorer with search results for the "SQL Server Top Features" (the change of the path to Sui T your PC):

"Well, this can being probably useful stuff, maybe this feature are really unfairly forgotten", thought the DBA while going th Rough the dark empty parking lot to her lonely car. "As someone really wise once said:" It is, we think we know that keeps us from learning. Learn, Unlearn and relearn ".

SQLCMD mode:give it one more chance