Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

An Introduction to the SQLCMD Mode in SSMS (SQL Spackle) Expand / Collapse
Author
Message
Posted Saturday, May 04, 2013 1:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1449454
Posted Sunday, May 05, 2013 11:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1449586
Posted Monday, May 06, 2013 5:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 35,952, Visits: 30,241
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449657
Posted Monday, May 06, 2013 5:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 3:14 PM
Points: 58, Visits: 52
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.
Post #1449668
Posted Monday, May 06, 2013 5:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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>



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1449679
Posted Monday, May 06, 2013 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1449727
Posted Monday, May 06, 2013 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1449728
Posted Monday, May 06, 2013 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 PM
Points: 6,544, Visits: 8,758
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1449731
Posted Monday, May 06, 2013 12:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 3,436, Visits: 1,683
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 . It's funny how close your first paragraph and mine were though!

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1449834
Posted Monday, May 06, 2013 3:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 20,458, Visits: 14,080
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1449915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse