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
  • 2
    did you look in msdb.dbo.sysalerts? Commented Sep 27, 2018 at 15:38
  • Yes this was the first place I looked. Unfortunately it contains no history or the specific cause of the alerts. Commented Sep 28, 2018 at 8:58

1 Answer 1

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.

1
  • Exactly what I was looking for, thank you! Commented Oct 4, 2018 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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