4

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?

0

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.

  • 1
    Unfortunately not. The problem is not with Powershell's interpretation of the literal, but that the SQL Agent substitutes the literal $ESCAPE_SQUOTE(WMI(State)) with a value that it determines before the step starts but, if that value does not exist for any reason, it won't even start the step. This leaves no way to guard against this value not existing from within the step code. – jimbobmcgee Jan 6 '18 at 18:52

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.