Help with SQL query in Alert

I'm inserting information into an email alert via a SQL query. I have the query referencing 2 tables and inner joining them. The query works fine, but placed inside an alert it doesn't work right.

for example:select Last_Name, First_Name, Company, Job_Title, Business_Phone, Moblie_Phonefrom Contacts inner join Nodes on Contacts.Company = Nodes.Coopwhere Caption = 'Name_of_Node' This query works, but will not work in an alert. Can anyone please explain why?

Find more posts tagged with

sql

advance_alerts

alert_engine

Accepted answers

Karlo.Zatylny

Hi,

The SQL macro is expecting just one return value. It will take the first value from the first row returned if more than one row or column is specified. Is this the behavior you are seeing?

You could repeat the SQL macro for each scalar item you are wanting to get in the message.

Please let me know if this works for you.

Thanks

All comments

Kyce

Same issue I am having. Out of about 30 emails I have gotten it to work one time. Not certain what I did different.

This is an example of what I have in my alert action:

${SQL:SELECT Caption from Interfaces WHERE (Interfaces.InterfaceID = '143' OR Interfaces.InterfaceID = '145' OR Interfaces.InterfaceID = '146' OR Interfaces.InterfaceID = '147') AND Interfaces.Status = '2'}

When it did "Work" it only returned the first row. Have since tried just returning only one row by combing multiple rows into a single one and still no luck.

Karlo.Zatylny

Hi,

The SQL macro is expecting just one return value. It will take the first value from the first row returned if more than one row or column is specified. Is this the behavior you are seeing?

You could repeat the SQL macro for each scalar item you are wanting to get in the message.

Please let me know if this works for you.

Thanks

Kyce

Tried with SELECT TOP ONE with several different queries so only one return value and still no luck.

dhardy

This worked for me.

It's not pretty, but it works perfectly:

Local Contact 1: ${SQL:SELECT First_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'} ${SQL:SELECT Last_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}
${SQL:SELECT Job_Title FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}
Office: ${SQL:SELECT Business_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}
Mobile: ${SQL:SELECT Moblie_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}
Home: ${SQL:SELECT Home_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}
Email: ${SQL:SELECT Email_Address FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '1'}

Local Contact 2: ${SQL:SELECT First_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'} ${SQL:SELECT Last_Name FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}
${SQL:SELECT Job_Title FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}
Office: ${SQL:SELECT Business_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}
Mobile: ${SQL:SELECT Moblie_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}
Home: ${SQL:SELECT Home_Phone FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}
Email: ${SQL:SELECT Email_Address FROM Contacts inner join Nodes on Contacts.Company = Nodes.Coop WHERE Contacts.Company = '${Nodes.Coop}' and Contacts.Contact_Num = '2'}

euphorik

Same thing I'm experiencing here... Attempting to page out a switch that is down, but include in the page-out the affected ports (ie. customers) so the on-call person can determine severity. Unfortunately, as mentioned above, the query returns all ports in my SQL query engine, but the macro only display's the first returned value. :-(

Any plans to modify this behavior in the future? Or do I need to figure out how to iterate through my interface list using a separate query for each interface? Ugh....

euphorik

Forgot to include the simple query in my alert.

Affected Customers
${SQL: Select InterfaceAlias From Interfaces where NodeID = ${NodeID}}

mrjohnson

I am working with dhardy to repair his query as it no longer works after an update to Orion. (Note this is a SQL statement in an email sent by an alert meant to give the contact information for the managers of a node)

{SQL:SELECT First_Name FROM Contacts INNER JOIN Nodes ON Contacts.Company = Nodes.ContactGroup WHERE Contacts.Company = '${ContactGroup}' AND Contacts.Contact_Num = 1}

now returns:

MACRO SQL ERROR -Unclosed quotation mark after the character string 'XXXX'.
Incorrect syntax near 'XXXX'.' AND Contacts.Contact_Num = 1

If I try to remove the quotation marks i.e.

WHERE Contacts.Company = ${ContactGroup} AND Contacts.Contact_Num = 1}

I get:

MACRO SQL ERROR - Invalid column name 'XXXX'. AND Contacts.Contact_Num = 1

I have tried a few other combinations/ordering of parenthesis, brackets, quote marks with no luck.

Why does the error message say invalid column name? Shouldn't the statement be matching a field and not a column? It grabs the correct character string (XXXX), yet it seems to try to grab everything to the right of WHERE statement and tack it on to the string. I can't figure out how to get the query to execute the AND statement as it did before.

Any help would be much appreciated as I am unsure if this is a bug or what since it used to work.

euphorik

I'm not really sure here, but have you also tried prepending the Table name to the ContactGroup inside the brackets like you did in the join statement?

ie. ${Nodes.ContactGroup}

It's possible the alert isn't getting the table reference passed through so you'll need to be explicit about it. Generally, I've found you can drop the Nodes.xxxx table reference, but you need to explicitly reference other tables if you use them. I'd throw it in anyway.

michael.fusco

So here is the SQL Query I put into a Text Only Email Alert to print out in the body a SQL Table (multiple column, rows)

${SQL:Select (Column1 + char(9) + Column2 + char(9) + Isnull(Column3,'') + char(9) + isnull(Column4,'') + char(9) + Column5 + char(10)) FROM Table where NodeID = '${NodeID}' Group By Column1, Column2, Column3, Column4, Column5 FOR XML RAW(''), ELEMENTS}

char(9) is ASCII TAB

char(10) is line feed (note char(13) is not needed)

isnull insures any columns which can be null are printed as a blank ''

end result is, yes the email query is only grabbing the first value, but that first value is the Whole Table. yes there is some alignment problems, but if someone wants to define padding options that be nice, but it works for me as is.

adam.beedell

Hi There,

Here's a hidden fix to something that's been a headache of mine for a while - Looks like you've not signed in for a while, but if you happen to still get these notifications i'd love to know more about how this workaround gets formatted