none
Passing varibles with special characters and or embedded spaces in powershell 2.0 with SQL Server

    Question

  • Hello all,

    So, I am trying to pass the date over to my sql table but it gives me errors saying invalid format, and it seems to be because special characters or embedded space.

    Here is what we are trying to do.

     

    # Gets the date

    $test_date = Get-Date -format G

    # Inputs the date into our sql query that injects them into our Queue length table.

    sqlcmd.exe -i ./test.sql -v  test_date=$test_date

    test.sql has this on it.

    print($test_date)

     

    I get the following error

     

    Sqlcmd: 'test_date=10/2/2012 2:48:39 PM': Invalid argument. Enter '-?' for help.

    ( I have a screenshot but the forum seems to not want me to enter it....

    Thursday, October 04, 2012 3:31 AM

Answers

  • Close but not quite.

    sqlcmd.exe -i ./test.sql -v test_date='$test_date'

    Just place single quotes aropung. it.  this is required witj an balues that may have spaces or are strings.  SQLServer expects all strings to have single quotes.

    Double quotes will not work the same way.

    THink of it this way.  POwerSHell is gong to expmad teh variable t a string when it evaluates teh line.

    This:

    sqlcmd.exe -i ./test.sql -v test_date='$test_date'

    will become this:

    sqlcmd.exe -i ./test.sql -v test_date='10/4/2012 12:47:53 AM'


    ¯\_(ツ)_/¯

    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 4:53 AM
  • Here is a complete solution:

    & cmd /c "sqlcmd.exe -i ./test.sql -v test_date=`"'$test_date'`" -E -S .\sqlexpress"

    Note that we have to use CMD to force the command to execute correctly  THe issue is evaluating around all of teh required quoting styles.

    Using '&' causes the string to be evalauted and executed and tusing CMD/C forces the quotes to be honored and preserved.

    test.sql contains only this:
    print $(test-date)

    We can also do this:
    $z="sqlcmd.exe -i ./test.sql -v test_date=`"'$test_date'`" -E -S .\sqlexpress"
    & cmd /c $z

    And this also works with this method:

    PS>$z="sqlcmd.exe -i ./test.sql -v test_date=`"'$([datetime]::Now)'`" -E -S .\sqlexpress"
    PS>& cmd /c $z
    10/04/2012 01:33:41


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 5:34 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 5:32 AM
  • I must have done it wrong then.  When I tried single quotes, it does not seem to convert my variable, instead it displays it without parsing.  It's why I had to use the ` backtick to prevent powershell from doing that.  With back-ticks the double quote

    You are close.  We actually need ot evaluate all bits at three levels.

    SQLCMD requires single quotes if tehre are spaces in teh string.  The commandline requires quotes on all strings.  Some MS documetnation is either wrong or in conflict with how this works.

    IN CMD we must use this:

    sqlcmd -v name="joe"

    If there is a space in teh name then add the singles

    sqlcmd -v name="'joe smith'"

    in PowerShell add the backticks:
    $cmd=sqlcmd -v name=`"'joe smith'"`

    and enclose in a string then execute with CMD /c


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 5:41 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 5:41 AM
  • Actually there is at least one alternate method:

    Invoke-Sqlcmd -InputFile .\Test.sql -server .\sqlexpress -Variable "testvar='$(Get-Date)'"

    #test.sql
    select $(testvar) as Var1;

    Variation # 2 with PSSQL

    Invoke-Sqlcmd -InputFile .\Test.sql -server .\sqlexpress -Variable "testvar='$(Get-Date)'" -Verbose

    #test.sql
    select $(testvar) as Var1;
    print 'now printing variable with "print" statement';
    print $(testvar);

    Note how we have to use single quotes now.


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 9:45 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 9:40 AM

All replies

  • Close but not quite.

    sqlcmd.exe -i ./test.sql -v test_date='$test_date'

    Just place single quotes aropung. it.  this is required witj an balues that may have spaces or are strings.  SQLServer expects all strings to have single quotes.

    Double quotes will not work the same way.

    THink of it this way.  POwerSHell is gong to expmad teh variable t a string when it evaluates teh line.

    This:

    sqlcmd.exe -i ./test.sql -v test_date='$test_date'

    will become this:

    sqlcmd.exe -i ./test.sql -v test_date='10/4/2012 12:47:53 AM'


    ¯\_(ツ)_/¯

    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 4:53 AM
  • Here is a complete solution:

    & cmd /c "sqlcmd.exe -i ./test.sql -v test_date=`"'$test_date'`" -E -S .\sqlexpress"

    Note that we have to use CMD to force the command to execute correctly  THe issue is evaluating around all of teh required quoting styles.

    Using '&' causes the string to be evalauted and executed and tusing CMD/C forces the quotes to be honored and preserved.

    test.sql contains only this:
    print $(test-date)

    We can also do this:
    $z="sqlcmd.exe -i ./test.sql -v test_date=`"'$test_date'`" -E -S .\sqlexpress"
    & cmd /c $z

    And this also works with this method:

    PS>$z="sqlcmd.exe -i ./test.sql -v test_date=`"'$([datetime]::Now)'`" -E -S .\sqlexpress"
    PS>& cmd /c $z
    10/04/2012 01:33:41


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 5:34 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 5:32 AM
  • I must have done it wrong then.  When I tried single quotes, it does not seem to convert my variable, instead it displays it without parsing.  It's why I had to use the ` backtick to prevent powershell from doing that.  With back-ticks the double quote

    You are close.  We actually need ot evaluate all bits at three levels.

    SQLCMD requires single quotes if tehre are spaces in teh string.  The commandline requires quotes on all strings.  Some MS documetnation is either wrong or in conflict with how this works.

    IN CMD we must use this:

    sqlcmd -v name="joe"

    If there is a space in teh name then add the singles

    sqlcmd -v name="'joe smith'"

    in PowerShell add the backticks:
    $cmd=sqlcmd -v name=`"'joe smith'"`

    and enclose in a string then execute with CMD /c


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 5:41 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 5:41 AM
  • See - it isn't as esay as you thought it was.

    LIke I said. The documentaion is extremelty clear except when it is wrong.  The correct versions always note that both sets of quotes are needed.  Add ot that the fact that PowerSHell is going to try and eat on set of quotes so we have to fool PosH into leaving the quotes alone and yet have it 'stringify' the variable.  To accomplish this we need to send the whole string to CMD/C.

    I have tried many other methods and this is the only one that appears to work under all circumstances even thuogh we would like to believe that ther is another way to do it.


    ¯\_(ツ)_/¯

    Thursday, October 04, 2012 9:21 AM
  • Actually there is at least one alternate method:

    Invoke-Sqlcmd -InputFile .\Test.sql -server .\sqlexpress -Variable "testvar='$(Get-Date)'"

    #test.sql
    select $(testvar) as Var1;

    Variation # 2 with PSSQL

    Invoke-Sqlcmd -InputFile .\Test.sql -server .\sqlexpress -Variable "testvar='$(Get-Date)'" -Verbose

    #test.sql
    select $(testvar) as Var1;
    print 'now printing variable with "print" statement';
    print $(testvar);

    Note how we have to use single quotes now.


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, October 04, 2012 9:45 AM
    • Marked as answer by coopar13 Thursday, October 04, 2012 10:56 AM
    Thursday, October 04, 2012 9:40 AM