8

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.

  • Could you please isolate the exact line that's causing a problem? There's no way any reader of this question could reliably reproduce this. – Jon Seigel Feb 11 '14 at 18:25
8

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.

  • Doh! I was hit by this as well $() is the same token that SQLCMD and other command line tools use to inject OS environmental variables into SQL scripts called by batch files. – Rawheiser Dec 19 '14 at 18:22
  • IMO this isn't in the docs because it's a bug! – Dave Markle Feb 6 '17 at 21:42
  • 1
    @DaveMarkle can't be a bug if it is in the docs...BOL link is provided in my answer. – Shawn Melton Feb 7 '17 at 2:06
  • Ugh, I just get so mad when things like this get designed into a product. OK. Not a bug. An "alternative feature". – Dave Markle Feb 7 '17 at 17:16
1

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)`'
  • It is within a here-string which does not treat it as a special character, takes it as is. Adding the grave-accent though has no affect, still receive the same syntax message by SQL Agent for the same line number. – Shawn Melton Feb 11 '14 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.