SQL Agent - PowerShell step "syntax error"

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.DriveLet

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.

StackExchange Database Administrators Q#58669, answer score: 18