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

  • 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

  • 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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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>

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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

  • 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

  • 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]

  • 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

  • Wonderful.

    Share a website with you ,

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

    Believe you will love it.

    We accept any form of payment.

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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

  • 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

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

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