Post reply

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

  • 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

  • May 5, 2013 at 11:36 pm

    #1612518

    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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 6, 2013 at 5:09 am

    #1612575

    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)

  • May 6, 2013 at 5:32 am

    #1612586

    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.

  • May 6, 2013 at 5:55 am

    #1612597

    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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 6, 2013 at 8:00 am

    #1612639

    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

  • May 6, 2013 at 8:01 am

    #1612640

    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

  • May 6, 2013 at 8:03 am

    #1612643

    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

  • May 6, 2013 at 12:20 pm

    #1612731

    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]

  • May 6, 2013 at 3:48 pm

    #1612799

    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

  • May 6, 2013 at 7:49 pm

    #1612830

    Wonderful.

    Share a website with you ,

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

    Believe you will love it.

    We accept any form of payment.

  • May 6, 2013 at 8:06 pm

    #1612834

    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)

  • May 7, 2013 at 9:51 am

    #1613053

    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

  • May 7, 2013 at 9:52 am

    #1613054

    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

  • May 7, 2013 at 9:54 am

    #1613056

    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 21 total)

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