passing path to SqlCmd within powershell script

Question

Im 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 Im calling: $path = 'C:\path' sqlcmd -SMySQLServerInstance -i 'MySqlScript.sql' -v MyVariablePath=$path

when run I recieve 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?

Answer 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.

Answer 2

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