Powershell – CmdExec Job Step Escape Double Quotes

jobspowershellsql serversql-server-agent

I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with getting the escape characters right, or tweaking my code.

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath `"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip`" -CompressionLevel Optimal"

I've also tried it using \ as the escape character

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath \"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip\" -CompressionLevel Optimal"

I tried running this script (with \ escape characters) in the command line and it ran fine. Thanks again for any help.

Best Answer

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found]. The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent.

Referencing your attempts,

$(get-date -f yyyy-MM-dd).zip

notice the $( where you're trying to get the current date for the zip file. That syntax is a Powershell sub-expression. This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($ZipDate) first and then use that to finish the Powershell command.

Try this:

powershell.exe "$ZipDate = (get-date -f yyyy-MM-dd); Compress-Archive -Path "C:\TestCompress" -DestinationPath "c:\TestCompressOut\$ZipDate.zip" -CompressionLevel Optimal"

Related Solutions

Sql-server – SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example: enter image description here

With your code I believe you can simply configure the step as shown below: enter image description here

Sqlcmd command strips $ out of query variable

Have you tried defining $InstanceName as a string literal with single quotations?

PS C:\> $GoodInstanceName = '$myInstanceName'
PS C:\> "Hostname$GoodInstanceName"
Hostname$myInstanceName

PS C:\> $BadInstanceName = "$myInstanceName"
PS C:\> "Hostname$BadInstanceName"
Hostname

In Powershell, string literals that are defined with double-quotes will resolve variables. However, if they are defined with single-quotes, they will not. Since $myInstanceName resolved in the $BadInstanceName example but has never been defined, it will simply resolve to an empty string.

I suspect this (or something similar) is the problem since the error seems to do with your completed path string.

Best Answer

Related Solutions

Sql-server – SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Sqlcmd command strips $ out of query variable

Related Question