passing path to SqlCmd within powershell script - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: PowerShell & .ps1 (https://0day.red/Forum-PowerShell-ps1) +--- Thread: passing path to SqlCmd within powershell script (/Thread-passing-path-to-SqlCmd-within-powershell-script) |
passing path to SqlCmd within powershell script - judgements320107 - 07-21-2023 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? RE: passing path to SqlCmd within powershell script - Mrsoucy0 - 07-21-2023 You will find several options to this issue on the Workarounds tab on [this issue filed on the Microsoft connect site][1]. While you're there please vote it up. [1]: [To see links please register here] RE: passing path to SqlCmd within powershell script - obedience197 - 07-21-2023 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][1] [1]: [To see links please register here] RE: passing path to SqlCmd within powershell script - Sirour184 - 07-21-2023 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 RE: passing path to SqlCmd within powershell script - anchovettas468199 - 07-21-2023 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]( [To see links please register here] ) 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 |