0

We have configured Alerts for the major severities and error codes on our SQL Server instances. Each alert then runs the same SQL Server Agent job, to trigger an alert in our monitoring system (for SMS messaging).

It would be useful to be able to retrieve the alert description text (see "This was just a test!" in the example below) from somewhere, to feed through to our monitoring system.

DATE/TIME:   27/09/2018 16:08:19
DESCRIPTION: Error: 50000 Severity: 20 State: 1 This was just a test!
COMMENT:     (None)
JOB RUN:     SystemAlerts

Is this text stored anywhere in the msdb database? Or is it not persisted like SQL Server Agent job history?

  • 2
    did you look in msdb.dbo.sysalerts? – Jonathan Fite Sep 27 '18 at 15:38
  • Yes this was the first place I looked. Unfortunately it contains no history or the specific cause of the alerts. – James D Sep 28 '18 at 8:58
1

Tokens may be what you're looking for:

SQL Server Agent token replacement is simple and efficient: SQL Server Agent replaces an exact literal string value for the token. All tokens are case-sensitive. Your job steps must take this into account and correctly quote the tokens you use or convert the replacement string to the correct data type.

Using $(ESCAPE_SQUOTE(A-MSG)) should allow you to access the description from an error message when the agent job is triggered by an alert.

  • Exactly what I was looking for, thank you! – James D Oct 4 '18 at 8:26

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.