passing path to SqlCmd within powershell script

Question

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 sqlcmd's -v switch. The problem is that powershell is doing something weird when I pass a path as the value of one of my variables which in turn causes the SQL script to fail.

For example I'm calling:

$path = 'C:\path'
sqlcmd -SMySQLServerInstance  -i 'MySqlScript.sql'  -v MyVariablePath=$path

when run I receive a error which says:

Sqlcmd: ':\path': Invalid argument.

No amount of double or single quotes that I have tried around $path or MyVariablePath=$path solves the issue.

Can somebody provide a simple canonical example of how this needs to be done?

I'm having the same issue and I haven't been able to find a solution anywhere in the internet. My current powershell version is 5.1. — Balasubramanian S
– Balasubramanian S, Commented May 26, 2021 at 14:48

Dav Evans · Accepted Answer · 2010-09-21 18:21:56Z

21

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

answered Sep 21, 2010 at 18:21

Dav Evans

4,0617 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jake Over a year ago

Are the quotes supposed to be mismatched like that?

Balasubramanian S Over a year ago

This solution did not work for me. I'm using 5.1 version of powershell.

tobias_k · Accepted Answer · 2014-06-30 12:55:49Z

3

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

edited Jun 30, 2014 at 12:55

tobias_k

83.1k12 gold badges130 silver badges186 bronze badges

answered Jun 30, 2014 at 12:26

Andrei Shakh

1814 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:55:07Z

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 '-?' for help. 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 '-?' for help. 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

Keith Hill · Accepted Answer · 2010-09-17 13:37:32Z

1

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.

answered Sep 17, 2010 at 13:37

Keith Hill

204k44 gold badges370 silver badges377 bronze badges

1 Comment

Dav Evans Over a year ago

Thanks for the input Keith. I've voted up the MS connect item.

Collectives™ on Stack Overflow

passing path to SqlCmd within powershell script

4 Answers 4

2 Comments

Comments

Test SQL file test.sql

Set of my testing variables:

Testing of sqlcmd command

a) Test #1

b) Test #2

c) Test #3

d) Final Solution

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Test SQL file test.sql

Set of my testing variables:

Testing of sqlcmd command

a) Test #1

b) Test #2

c) Test #3

d) Final Solution

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related