Why does code like this execute in PowerShell ISE:
$var = 'dog';Invoke-SqlCmd -ServerInstance 'servername' -Query "insert into DB.dbo.tbl values ('$($var)')"
But not in SQL Server Agent as a CmdExec job step? Despite enclosing it as per usual. I believe it is the multiple queries as individual queries work
powershell.exe -ExecutionPolicy Bypass -Command {$var = 'dog';Invoke-SqlCmd -ServerInstance 'servername' -Query "insert into DB.dbo.tbl values ('$($var)')"}
TL;DR;
I have a SQL Server Agent job with a CmdExec step. I wish to assign a variable and then use it in an insert statement.
So far the following insert will work:
powershell.exe -ExecutionPolicy Bypass -Command "Invoke-SqlCmd -ServerInstance 'servername' -Query 'insert into DB.dbo.tbl values (''cat'')'"
This inserts a cat. Now I need a dog, something along the lines of:
powershell.exe -ExecutionPolicy Bypass -Command "$var = 'dog'; Invoke-SqlCmd -ServerInstance 'servername' -Query ""insert into DB.dbo.tbl values (''$($var)'')"""
There are two issues here, the comma terminator which I have used successfully in other jobs to separate commands. The second issue is escaping the double quotes. I understand they are required to process the variable into the string.
I have tried escaping with double quotes as well as one of these `. I've also tried separating the variable declaration into one server agent job step and using the variable in another.
Based on @gvee answer below, I put the code in a script block, which executes perfectly in PowerShell, but not as a SQL Agent CmdExec job step
powershell.exe -ExecutionPolicy Bypass -Command {$var = '''dog'''; Invoke-SqlCmd -ServerInstance 'servername' -Query "insert into DB.dbo.tbl values ($($var))"}
Note I had to escape the variable three times, which inserted into the table as 'dog' with the quotes included. I removed the single quotes from the insert statement, however I cannot get this to run in Server Agent as a CmdExec step
