An Introduction to the SQLCMD Mode in SSMS (SQL Spackle)

Question

Post reply

An Introduction to the SQLCMD Mode in SSMS (SQL Spackle)

WayneS

SSC Guru

Points: 95460
More actions
May 4, 2013 at 1:10 pm

#277222

Comments posted to this topic are about the item An Introduction to the SQLCMD Mode in SSMS (SQL Spackle)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Hi Wayne.

Nice article, thank you.

Can you explain what you mean by: "...you can specify what the server is change all the variables at one place simply" please - it reads a little strangely to me.

Another thing of interest - is there a way of using SQLCMD mode to run a command on one server, capture the result in a variable and use that result in subsequent commands against other servers, within a single script?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

What a great introduction to the SQLCMD gui in SQL Server. Very nicely done, Wayne.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Shurts SSC Veteran Points: 292 More actions · Answer 3

Very interesting... I just had to play and it was fun. However, I can't think of any clever usage for it, yet. But give me time, I'm sure it will become an interesting part of my toolkit. The downside, if you can call it that, is that you have to specify the SQLCMD each and every time you want to run the script, thus restricting it usage for areas where you are trying to automate (e.g. - being able to check a directory for files and running a different stored proc if a file is there...). But cool, nonetheless.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 4

Steve Shurts (5/6/2013)
Very interesting... I just had to play and it was fun. However, I can't think of any clever usage for it, yet. But give me time, I'm sure it will become an interesting part of my toolkit. The downside, if you can call it that, is that you have to specify the SQLCMD each and every time you want to run the script, thus restricting it usage for areas where you are trying to automate (e.g. - being able to check a directory for files and running a different stored proc if a file is there...). But cool, nonetheless.

If you're thinking of automation, you might want to think beyond what SSMS can give you. There's no problem running SQLCMD scripts developed in SSMS from the command line, without worrying about setting modes - here's an extract from BOL to illustrate:

Connecting to a named instance by using Windows Authentication and specifying input and output files:
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

WayneS SSC Guru Points: 95460 More actions · Answer 5

Phil Parkin (5/5/2013)
Hi Wayne.
Nice article, thank you.
Can you explain what you mean by: "...you can specify what the server is change all the variables at one place simply" please - it reads a little strangely to me.

Wow, I can't believe that slipped through. Thanks for catching this Phil.

In looking back over the entire sentence

By invoking the “Specify Values for Template Variables” dialog (CTRL+SHIFT+M, or the third menu item in the first screenshot in this article of the Query menu), you can specify what the server is change all the variables at one place simply.

, I am using a SQLCMD variable (set with SETVAR) to store a server's name to connect to. This allows me to have the server specified in one location, and to just reference it in the scripts. In the saved script, the server name is saved in the format used by templates (in the :SETVAR line), so when I open the script I just do the CTRL+SHIFT+M to change the variables to their desired value, and it's done. Elsewhere in the script, I use the SQLCMD variable. True, I can save this same template in place of each place that I need to reference this server, but I find it easier to use a SQLCMD variable here.

Another thing of interest - is there a way of using SQLCMD mode to run a command on one server, capture the result in a variable and use that result in subsequent commands against other servers, within a single script?

Since you have to issue a GO after the connect to a subsequent server, a TSQL variable would be lost. And you cannot set SQLCMD variables in this manner. Once you change a connection, any other approach that I've been able to think of (global temporary table, OpenRowset to save to a fixed file) would be scoped to the server, so this also would not work. (OpenRowset would work if these were multiple instances on the same server.) So, I cannot think of any way of doing this. Perhaps other readers have a solution?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 6

Jeff Moden (5/6/2013)
What a great introduction to the SQLCMD gui in SQL Server. Very nicely done, Wayne.

Thanks Jeff

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

Steve Shurts (5/6/2013)
Very interesting... I just had to play and it was fun. However, I can't think of any clever usage for it, yet. But give me time, I'm sure it will become an interesting part of my toolkit. The downside, if you can call it that, is that you have to specify the SQLCMD each and every time you want to run the script, thus restricting it usage for areas where you are trying to automate (e.g. - being able to check a directory for files and running a different stored proc if a file is there...). But cool, nonetheless.

I'm glad you liked this Steve.

Two places where I use it right now:

1. Generic script for setting up log shipping

2. Performing a "dev refresh" by restoring a prod database into dev.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 8

Nice job introducing SQLCMD. I spent several hours this weekend trying to write up an intro to SQLCMD myself. You did a much better job than I was able to :-P. It's funny how close your first paragraph and mine were though! 🙂

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I really think this could have been several times better.

JK Wayne

Nice Job;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ccmalls21 SSC Rookie Points: 38 More actions · Answer 10

Wonderful.

Share a website with you ,

( http://www.ccmalls.net/ )

Believe you will love it.

We accept any form of payment.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 11

ccmalls21 (5/6/2013)
Wonderful.
Share a website with you ,
Believe you will love it.
We accept any form of payment.

Spam reported.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 12

Kenneth.Fisher (5/6/2013)
Nice job introducing SQLCMD. I spent several hours this weekend trying to write up an intro to SQLCMD myself. You did a much better job than I was able to :-P.

Thanks Kenneth

It's funny how close your first paragraph and mine were though! 🙂

Great minds thinking alike?

I'd love to see your writeup!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 13

SQLRNNR (5/6/2013)
I really think this could have been several times better.
JK Wayne
Nice Job;-)

True... but it would require timely feedback from those reviewing it before submission! :w00t::w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

WayneS (5/7/2013)
SQLRNNR (5/6/2013)
I really think this could have been several times better.
JK Wayne
Nice Job;-)
True... but it would require timely feedback from those reviewing it before submission! :w00t::w00t:

I was timely - just timely the way Oracle does things.:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events