Sql-server – Handling ‘Variable not found’ in a tokenised job step

powershellsql serversql-server-agent

I have an Agent job which is usually triggered by a WMI event. The job has a token within it, which automatically takes a value from the WMI event and substitutes it into the step's Powershell code, e.g.:

$alert_wmi_state = '$(ESCAPE_SQUOTE(WMI(State)))';

The SQL Agent is configured with the Replace tokens for all job responses to alerts setting checked so, when the WMI event triggers, the substitution happens fine, and the code that is run is:

$alert_wmi_state = '7';

However, there is the possibility that this job could be run manually (i.e. right-click, Start Job at step…) and I would like my step code to handle this gracefully, so I have this logic further in my step:

if ($alert_wmi_state -eq ('$' + '(ESCAPE_SQUOTE(WMI(State)))')) {
    ...
}

…the theory being that, splitting $ from (ESCAPE_SQUOTE... would reasonably mean that this is not recognised as a token, and so does not get substituted, so the test would read literally:

if ($alert_wmi_state -eq '$(ESCAPE_SQUOTE(WMI(State)))') {
    ...
}

However, when running the job manually, the SQL Agent always errors out at the step (regardless if it's configured as a CmdExec job step type or a PowerShell job step type) before any of my code is run, with the error Variable WMI(State) not found.

My guess is that this is because the substitution of ESCAPE_SQUOTE happens before the step code is actually parsed/loaded, so the Agent never runs the step.

I expect I could have two steps, one with the token and one without, and have the failure action of the tokenised one start the non-tokenised one, but that duplication of step code is not desirable.

So, my question is, is there any way to gracefully handle the non-existence of a step token within the step code?

Best Answer

Would using a PowerShell here-string in your script prevent the need for ESCAPE_SQUOTE?

$alert_wmi_state = @'
$(WMI(State))
'@

That should at least let you determine if the problem is with ESCAPE_SQUOTE throwing an error or if it's SQL Agent's token parser itself that's complaining.

As long a the SQL Agent doesn't literally begin with '@ you should be fine. If it does begin with '@, you can fix that easily:

$alert_wmi_state = @'
_$(WMI(State))
'@.Substring(1)

Note that you typically want to use @'...'@ and not @"..."@ since the latter is still evaluated by PowerShell like a double quoted string.

Related Question