Alerting Help: MACRO SQL ERROR - Incorrect syntax near...

This alert has been in place for years without issues; now all of sudden I'm getting MACRO SQL ERROR - Incorrect syntax near...

This the definition of the Alert Message:

ALERT: ${NodeName} ${SQL:Select Substring ('${Caption}',1,30) AS Caption} Volume is ${SQL:Select Round ('${VolumePercentUsed}',0) from Volumes WHERE VolumeID='${VolumeID}'}% Utilized

This is the output I started getting recently

ALERT: AAAA000 MACRO SQL ERROR - Incorrect syntax near '&'. Volume is MACRO SQL ERROR - Incorrect syntax near '&'.% Utilized

Find more posts tagged with

sam

sql

macro

macro sql error

Alerting

Accepted answers

All comments

njoylif

did you upgrade or make any changes recently?

without researching, I'm guessing the "'" is either a single or double quote. try replacing with actual single quotes. if not quote, remove all instances and test

spzander

No changes other than removing contacts from the alert list.

ADDITION INFORMATION: This works fine in email alerts(plain and HTML subject and body). It's no longer working for SMS/TEXT alerts, this is when the MACRO SQL ERROR occurs.

This is the original message format/syntax, it's converting the quotes to character code(in SMS/TEXT only) for SQL apparently.

ALERT: ${NodeName} ${SQL:Select Substring ('${Caption}',1,30) AS Caption} Volume is ${SQL:Select Round ('${VolumePercentUsed}',0) from Volumes WHERE VolumeID='${VolumeID}'}% Utilized

If I paste this into a SMS/TEXT action message field it changes the ' into &#39 after closing the window.

natetech

I tried the E-mail Simulate and Execute against the node that was showing this issue.

Both of those tests were successful, but any alert automatically generated was displaying the error.

I realized that my node name had changed, so the original event just kept firing.

I went to events and deleted all entries with the old name, and the next time the alert fired, it displayed correctly.