Passing Path To Sqlcmd Within Powershell Script

I'm trying to write a powershell script which will execute sqlcmd.exe to run a sql script. The script contains a SQLCMD variable which I want to pass in on the command line via sql

Solution 1:

finally worked it out. for the next sucker to try this here is the solution

powershell script looks like

$myPath = "`"C:\Path`"" 
sqlcmd.exe -SmySQLInstance -i./test.sql -v myvar=$myPath

my test.sql file can then use the variable like this

PRINT "$(myvar)"

the key here is understanding how powershell does escape characters. More info on that here

Solution 2:

Had the same issue, found the solution accidentally, still don't understand why it works :) (i'm not a powershell pro though):

sqlcmd -d ... -s ... -v Var1Name=("""$PowershellVar1""") Var2Name=("""$PowershellVar2""")

Variables $PowershellVar1 and $PowershellVar2 have string type in my PS script and can contain quotes, spaces etc

Solution 3:

You will find several options to this issue on the Workarounds tab on this issue filed on the Microsoft connect site. While you're there please vote it up.

Solution 4:

I was recently playing around with this problem. In my case, I had variables with dots and spaces inside them. I will list all combinations I tried to make it run.

Test SQL file test.sql

declare @testvar varchar(30);
set @testvar = '$(testvar)';
print @testvar;

Set of my testing variables:

$varA = 'Abc1.3,Abc4.3'# contains only dots$varB = 'A bc1.3,Ab c4.3'# contains dots and spaces$varC = 'xx   x.yy,y,.1.2.,3 ,  y'# contains dots and multiple spaces

Testing of sqlcmd command

sqlcmd -v testvar=`"$var`" -i test.sql
sqlcmd -v testvar=($var) -i test.sql
sqlcmd -v testvar=("""$var""") -i test.sql     # Solution by Andrei Shakh

a) Test #1

First I found out that my powershell script is returning error on variable that contain spaces

sqlcmd -v testvar=`"$varA`" -i test.sql
Abc1.3,Abc4.3

sqlcmd -v testvar=`"$varB`" -i test.sql
sqlcmd : Sqlcmd: 'testvar="A bc1.3,Ab c4.3""': Invalid argument. Enter '-?'forhelp. At line:2 char:1

b) Test #2

Finally found solution to replace variable by parenthesis instead of double-quotes, BUT!

sqlcmd -v testvar=($varA) -i test.sql
sqlcmd : Sqlcmd: ',Abc4.3': Invalid argument. Enter '-?'forhelp. At line:1 char:1

sqlcmd -v testvar=($varB) -i test.sql
A bc1.3,Ab c4.3

Interestingly enough, I've found out that this solution isn't working with with dots in my variables.

c) Test #3

I made a script to match space in variable and in that case use parenthesis, which works both ways.

If ($var -match " ")                           # or ($var -like "* *")
{
    sqlcmd  -v testvar=($var) -i test.sql
}
Else
{
    sqlcmd  -v testvar=`"$var`" -i test.sql
}

d) Final Solution

So far best solution I've found was answer by Andrei Shakh here, which works with everything without using IF/ELSE statemens to check whether there is space in a string or not.

sqlcmd -v testvar=("""$varA""") -i test.sql
Abc1.3,Abc4.3

sqlcmd -v testvar=("""$varB""") -i test.sql
A bc1.3,Ab c4.3

sqlcmd -v testvar=("""$varC""") -i test.sql
xx   x.yy,y,.1.2.,3 ,  y