SQL Agent - PowerShell step "syntax error"

Question

I have the following code setup in SQL Agent Job step that is not running. The message received is simply:

Unable to start execution of step 1 (reason: line(46): Syntax error). The step failed.

The duration of the job is: 00:00:00

Line 46 of this script is a here-string:

    ,@DriveLetter = '$($c.DriveLetter)'

This script runs perfect outside of SQL Server Agent.

ServerNames table:

CREATE TABLE ServerTable (
ServerName varchar(50)
)

Disk Space table would be something similar to:

CREATE TABLE DiskSpace (
ID int IDENTITY(1,1),
ServerName varchar(50),
DriveLetter varchar(2),
DiskSpaceCapacityGB decimal(12,5),
DiskSpaceFreeGB decimal(12,5)
)

PowerShell Script:

This command is to allow SQL Agent job to show failure in event PowerShell script errors
Default behavior in SQL Agent for PowerShell steps it 'Continue'
$erroractionpreference = "Stop"


$sqlInstance = 'server1'
$sqlDatabase = 'database1'

$qServerList = @"
Select ServerName From ServerTable
"@

$srvList = Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qServerList | 
    Where-Object {$_ -ne '' -or $_ -ne $null} | Select-Object -ExpandProperty ServerName

foreach ($s in $srvList)
{
    if (Test-Connection -ComputerName $s -Count 1 -ErrorAction 'SilentlyContinue')
    {
        try 
        {
            $cServer = Get-WmiObject Win32_Volume -ComputerName $s -ErrorAction 'Stop' |
                where {$.DriveType -eq 3 -and $.DriveLetter } |
                Select-Object @{Label="ServerName";Expression={$s}},
                    @{Label="DriveLetter";Expression={$.DriveLetter}},
                    @{Label="DiskSpaceCapacityGB";Expression={"{0:N0}" -f($.Capacity/1GB)}},
                    @{Label="DiskFreeSpaceGB";Expression={"{0:N2}" -f($_.FreeSpace/1GB)}}

        foreach ($c in $cServer) 
        {
            $qAddServerDiskSpace = @"


EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

            try
            {
                Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddServerDiskSpace -ErrorAction 'Stop'
            }
            catch
            {
                $ErrorMsg = $_.Exception.Message
                $fullMsg = "Error writing executing dbo.StoredProcInsert for $s - $ErrorMsg"
                Return $fullMsg
            }           
        }
    }
    catch
    {
        $ErrorMsg = $_.Exception.Message
        $qAddPSErrorLog = @"


EXEC [dbo].[StoredProcInsert_Error] 
    @ServerName = '$($cServer.ServerName)'
    , @ErrorText = '$($ErrorMsg)'
"@
            try 
            {
                Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddPSErrorLog -ErrorAction 'Stop'
            }
            catch 
            {
                $ErrorMsg = $_.Exception.Message
                $fullMsg = "Error writing executing dbo.StoredProcInsert_Error for $s - $ErrorMsg"
                Return $fullMsg
            }
        }
    }
    else
    {
        $qAddPSErrorLog = @"
EXEC [dbo].[StoredProcInsert_Error] 
    @ServerName = '$($cServer.ServerName)'
    , @ErrorText = 'Unable to ping server'
"@

    try 
    {
        Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddPSErrorLog  -ErrorAction 'Stop'
    }
    catch 
    {
        $ErrorMsg = $_.Exception.Message
        $fullMsg = "Error writing executing dbo.StoredProcInsert_Error for $s - $ErrorMsg"
        Return $fullMsg
    }
}


}

EDIT

Setup additional steps to try using CmdExec type as PowerShell -Command "& { }". With this execution the script runs and the error log is populated for the appropriate catch blocks, but no disk space data is written to the table.

The agent history for this run shows a warning message:

Message Executed as user: NT Service\SQLAgent$myInstance. WARNING: Some imported command names include unapproved verbs which might make them less discoverable. Use the Verbose parameter for more detail or type Get-Verb to see the list of approved verbs. Process Exit Code 0. The step succeeded.

If I use the same type step but call the file: PowerShell -File MyScript.ps1 I get the same message as the PowerShell step type above for syntax error.

Answer 1

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. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; .

So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed.

The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this:

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

would have to be modified to something like this:

$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$serverName'
   ,@DriveLetter = '$driveLetter'
   ,@DiskSpaceCapacityGB = $CapacityGB
   ,@DiskFreeSpaceGB = $freeGB
"@

Made the above adjustments to my script and it runs perfectly now.

Answer 2

The single quote (') is a special character for Powershell and delimits strings. The difference between double and single quotes is in a matter of how it then interprets the string. Since it is a special character, you will want to escape it using grave-accent (`). This syntax should work for you:

 @ServerName = `'$($c.ServerName)`'
,@DriveLetter = `'$($c.DriveLetter)`'