CODEWITHSUNDEEP
powershellsqlcmd
CSCoder
CSCoder

Reputation: 150

Passing path scripting variable to sqlcmd when called using Invoke-Expression

I'm trying to create a powershell script that creates a database. The relevant part of the createDb.ps1 is:

param([string] $server,
      [string] $dbName)
$scriptpath = "C:\script\path" 
$cDb = "master"
$line = "script.sql"
$outfile = "\log.txt"
$dbDir = "C:\database path\"
$command = @"
sqlcmd -b -S $server -d $cDb -i '$scriptpath\$line' -o '.$outfile' -v dbLocation='$dbDir' dbName=$dbName
"@

Invoke-Expression $command

I call the script with the following parameters:

createDb.ps1 -server localhost -dbName TestDb

However when I run this I get the following error: 

sqlcmd: 'dbDir=C:\database path\" dbName=TestDb': Invalid argument. Enter '-?' for help.

When I execute the following from the command line, everything works as expected:

sqlcmd -b -S localhost -d master -i "C:\script\path\script.sql" -o ".\log.txt" -v dbLocation="C:\database path\" dbName=TestDb

Upvotes: 1

Views: 2042

Answers (2)

mklement0
mklement0

Reputation: 437698

Do not build your command line as a string first and then pass it to Invoke-Expression - not only is it unnecessary, it causes problems with argument partitioning.

Invoke the command directly:

sqlcmd -b -S $server -d $cDb -i $scriptpath\$line -o ".$outfile" -v dbLocation=`"$dbDir`" `
  dbName=$dbName

Note how ".$outfile" and dbLocation=`"$dbDir`" require special treatment:

  • Due to a PowerShell parsing quirk (as of PSv5.1), .$ at the start of an unquoted token causes it to be broken into 2 arguments. Enclosing .$outfile in "..." prevents that problem.

  • After PowerShell has parsed your command, it essentially rebuilds the command line with selective double-quoting of arguments before passing it to the system for execution. While this usually works as desired, there are edge cases, such as dbLocation=$dbDir:

    • Since the value of $dbDir - C:\database path\ - contains spaces, PowerShell would enclose the expanded result of dbLocation=$dbDir in "..." to ensure that it is recognized as a single argument, which yields "dbLocation=C:\database path\" - and sqlcmd may balk at that.

    • By explicitly embedding double quotes in the token - using `", which are escaped " chars. - PowerShell leaves the expanded result of dbLocation=`"$dbDir`" alone, which yields: dbLocation="C:\database path\"

    • Caveat:

      • Most target programs interpret \" not as having syntactic function (in this case: not as a closing " that happens to be preceded by a \), but as an escaped, embedded " resulting in broken argument parsing - depending on the target program, you may have to escape that final \ - or possibly all \ instances - as \\.

      • It is important to understand that on Windows it is ultimately always up to the target program to interpret the command line; see this answer for more information.

Generally:

  • Variable references do not need double-quoting when they serve as arguments passed to an external utility, even when the referenced variables' values contain spaces (or other metacharacters).

    • That said, routinely double-quoting tokens that combine variable references with literals / other variable references is a good habit to form (e.g, "$scriptpath\$line" and ".$outfile"), because the precise rules when such tokens would otherwise be considered multiple arguments are not easy to remember - see the link at the bottom.

  • However, you need to be aware of unquoted instances of PowerShell's own metacharacters - such as , in general, and @ at the start of a token - and `-escape them to use them as literals (not an issue in the case at hand).

  • The quoting style matters only to PowerShell - once PowerShell has performed its own parsing and possibly expanded variable references, the then-literal tokens are reassembled into a command line that uses double quotes as needed behind the scenes to preserve argument boundaries, even if the original command line comprised single-quoted tokens.

    • For instance, PowerShell command line foo.exe 'bar baz' $env:ProgramFiles $env:OS would translate to foo.exe "bar baz" "C:\Program Files" Windows_NT on invocation of foo.exe.
      Note how double-quoting is employed on demand for the values with embedded spaces, irrespective of what quoting was used in the original command line.

For a comprehensive discussion of how PowerShell parses arguments, see this answer of mine.

Upvotes: 2

James Santiago
James Santiago

Reputation: 3062

Use double quotes instead to surround your attribute values when running sqlcmd:

$command = @" 
    sqlcmd -b -S $server -d $cDb -i "$scriptpath\$line" -o ".$outfile" -v dbLocation="$dbDir" dbName=$dbName 
"@

Upvotes: 0

Related Questions