How do you pass multiple variables into a SQL script through powershell?

Question

I have the following line to set up my sql cmd.

    $mySqlCmd = "sqlcmd -S $server -U $username -P $pwd   -d $dbname -o $lis -i $sqlScript -v fileDate = $fileDate, filePath = $filePath, sqlLoadErrors = $sqlLoadErrorPath"
Invoke-Expression $mySqlCmd

This doesn't seem to work for more than one variable. The SQL script runs and works if I remove the last 2 variables I am trying to pass into the SQL script.

On my SQL script I have the following:

DECLARE 
    @currentDate NVARCHAR(25), 
    @filePath NVARCHAR(25),
    @sqlLoadErrors NVARCHAR(25)

SET @currentDate = $(fileDate) 
SET @filePath = $(filePath)
SET @sqlLoadErrors = $(sqlLoadErrors)

Is there something wrong being done in the powershell script to pass variables to an SQL script?

I'd bet the commas are confusing the parser. I would try using PowerShell's stop parsing symbol, `--%`. Like so: `$mySqlCmd = "sqlcmd --% -S $server -U $username -P $pwd -d $dbname -o $lis -i $sqlScript -v fileDate = $fileDate, filePath = $filePath, sqlLoadErrors = $sqlLoadErrorPath"`. — Bacon Bits, Jun 12 '17 at 21:33
Also, `$pwd` is a reserved variable name for the current working directory. It's from the `pwd` POSIX shell command for "print working directory". I'd avoid using that. — Bacon Bits, Jun 12 '17 at 21:33
Doesn't seem to work. Thanks for the pwd tip though I changed that now. — user3266638, Jun 12 '17 at 21:46
Possible duplicate of [How do I pass multiple parameters into a function in PowerShell?](https://stackoverflow.com/questions/4988226/how-do-i-pass-multiple-parameters-into-a-function-in-powershell) — jac, Jun 12 '17 at 22:11

score 0 · Answer 1 · answered Jun 17 '17 at 19:01

try this :

$cmdparameters = @(
    'fileDate = "17/06/2017"',
    'filePath = "c:\temp\test.txt"',
    'sqlLoadErrors = "yourcontent"'
)

$mySqlCmd = "sqlcmd -S $server -U $username -P $pwd   -d $dbname -o $lis -i $sqlScript -v $cmdparameters"
Invoke-Expression $mySqlCmd

How do you pass multiple variables into a SQL script through powershell?

1 Answers1