I have some simple PowerShell code to insert a value into a table:
Invoke-SqlCmd -ServerInstance myserver -Query 'insert into Database.dbo.tbl values (1)'
and if I save it as a file, I can call it in a CmdExec job step. However, can anyone tell me why I can't run the command, especially as it is only one line:
powershell.exe -ExecutionPolicy Bypass -Command 'Invoke-Sqlcmd -ServerInstance myserver -Query ''insert into DBADatabase.dbo.tbl values (1)'''
Can anyone tell me why running as a command will not work, yet the same code as a file can work. Any help appreciated.
Thanks
Your powershell.exe ... command is executed from outside PowerShell, possibly via cmd.exe.
In that case, '...'-enclosed strings have no syntactical function and are passed straight through to PowerShell, causing it to interpret them as enclosing a string literal, which is simply output as-is (in other words: your command is printed rather than executed).
Instead, you must use "..." to enclose the command to pass to PowerShell; inside that string, you're free to use '...':
powershell.exe -ExecutionPolicy Bypass -Command "Invoke-Sqlcmd -ServerInstance myserver -Query 'insert into DBADatabase.dbo.tbl values (1)'"