Using SQLCMD Variables

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718085

    Comments posted to this topic are about the item Using SQLCMD Variables

  • WILLIAM MITCHELL

    SSChampion

    Points: 13685

    That does not match with this example:

    https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15

    and the example is:

    :setvar SQLCMDLOGINTIMEOUT 60

    :setvar server "testserver"

    :connect $(server) -l $(SQLCMDLOGINTIMEOUT)

    USE AdventureWorks2012;

    SELECT FirstName, LastName

    FROM Person.Person;

    ...there aren't any quotes. Is there a different behavior depending on the SQL version?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718085

    Good catch. Added the quotes to the SELECT.

    That example doesn't need quotes for the connect.  However, in selecting a value, it needs to be valid. In this case, the original question has this code executing:

    select log.txt, myid, myname, mychar from dbo.MyTable;

    Unless there's a column named log.txt, this is invalid. I've added quotes tot the SELECT. In the example you show, there isn't a need for quotes in the first variable, and neither part of the :connect line needs quotes.

  • Scott Coleman

    One Orange Chip

    Points: 27429

    The question was what should replace xxx in " SELECT 'xxx', ... ".

    There are already quotes around the xxx.  Why would I include another set of quotes around the SQLCMD variable reference?

     

    You can go ahead and try SELECT ''$(DirectoryR00tPath)'', but I don't think you'll be happy with the result.

  • Ryan C. Price

    Ten Centuries

    Points: 1182

    Scott Coleman wrote:

    The question was what should replace xxx in " SELECT 'xxx', ... ".

    There are already quotes around the xxx.  Why would I include another set of quotes around the SQLCMD variable reference?

    this.

    Having just spent a day writing code using sqlcmd variables (including variables for file/folder names), I answered with confidence, only to be told I was wrong ?!?!?!

    This won't even parse:

    :setvar DirectoryRootPath "Log.txt"


    SELECT ''$(DirectoryRootPath)''

    • This reply was modified 2 weeks, 3 days ago by  Ryan C. Price.
  • George Vobr

    SSCrazy Eights

    Points: 9126

    I had more time on Sunday, but I didn't believe I'd spend nearly half a day with it. I will try to explain this using the examples below, which I compiled on SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64). I included notes in the script code that helped me understand the correct answer. And here is a link to DOCS "sqlcmd - Use with Scripting Variables".

    --========================================
    -- Set Query in SQLCMD Mode First --
    --========================================
    -- Examples according to QotD 2020-03-20:
    ------------------------------------------

    :setvar DirectoryRootPath "C:\Log.txt"
    --SELECT $(DirectoryRootPath); -- Error because it's like SELECT C:\Log.txt
    SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT 'C:\Log.txt'
    GO

    :setvar DirectoryRootPath 'C:\Log.txt'
    SELECT $(DirectoryRootPath); -- O.K. as SELECT 'C:\Log.txt'
    --SELECT '$(DirectoryRootPath)'; -- Error because it's like SELECT ''C:\Log.txt''
    GO

    :setvar DirectoryRootPath C:\Log.txt
    --SELECT $(DirectoryRootPath); -- Error because it's like SELECT C:\Log.txt
    SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT 'C:\Log.txt'
    GO

    :setvar DirectoryRootPath """C:\Log.txt"""
    --SELECT $(DirectoryRootPath); -- Error Invalid column name 'C:\Log.txt'.
    SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT '"C:\Log.txt"'
    GO
    --====================================================================================
    -- Examples according to DOCS:
    ------------------------------

    :setvar tablename Person.Person
    :setvar col1 FirstName
    :setvar col2 LastName
    :setvar title Ms.
    USE AdventureWorks2014
    SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
    FROM $(tablename)
    WHERE Title = '$(title)' -- O.K. as SELECT 'Ms.'
    GO
    --=================================================================
    /*
    :setvar tablename Person.Person
    :setvar col1 FirstName
    :setvar col2 LastName
    :setvar title Ms.
    USE AdventureWorks2014
    SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
    FROM $(tablename)
    WHERE Title = $(title) -- Error because it's like SELECT Ms.
    GO
    */
    --=================================================================
    :setvar tablename Person.Person
    :setvar col1 FirstName
    :setvar col2 LastName
    :setvar title "Ms."
    USE AdventureWorks2014
    SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
    FROM $(tablename)
    WHERE Title = '$(title)' -- O.K. as SELECT 'Ms.'
    GO

    :setvar tablename Person.Person
    :setvar col1 FirstName
    :setvar col2 LastName
    :setvar title 'Ms.'
    USE AdventureWorks2014
    SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
    FROM $(tablename)
    WHERE Title = $(title) -- O.K. as SELECT 'Ms.'
    GO
    
    
    
    
    
    

    • This reply was modified 2 weeks, 2 days ago by  George Vobr.
    • This reply was modified 2 weeks, 2 days ago by  George Vobr.
  • hbrest

    Old Hand

    Points: 373

    I totally agree with Scott and Ryan.

    The question was not written properly.

    I also work a lot with SQLCMD variables and the given answer from this QotD is not correct.

     

Viewing 7 posts - 1 through 7 (of 7 total)

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