Alerting on SQL Server Insert

Hi,

I am trying to figure out a way to generate events each time a record is inserted into a Microsoft SQL Server table. The SQL Server User Experience Monitor is close to what I am looking for, but it seems to only allow for alerting on a numeric threshold value from a single query. There appears to be no way to pass the results of the returned dataset into the event message. Is there any way to get around this limitation, or am I possibly missing something?

Thanks

Find more posts tagged with

sql_server

sam

ssuem

Accepted answers

Petr.Vilem

What about using "Count as difference"?

With these settings component will change its status to warn after more rows than in previous poll are found in specified table. Then you can define appropriate alert action triggered on component (or application) status change. You can also use message returned as second column from SQL query which should then be available as macro in alerts.

All comments

aLTeReGo

I don't believe the SQL User Experience Monitor will allow you to receive an alert when a row is inserted into a table. You could use to to query the table length and set a threshold higher than the number of rows are are expected to be in the table and alert based on that, but this would require you to adjust the threshold every time a row is inserted to continue to be alerted of future inserts.

You might want to try posting something in the script lab. They may be able to point you in the right direction.

Petr.Vilem

What about using "Count as difference"?

aLTeReGo

for the win! That's certainly the simplest and most elegant solution by far. Great suggestion Petr!

jaytech

Very nice, that is pretty much what I am looking for. I will have to brush up on my SQL though since I am looking to include some of the row contents in the alert. I can't get the query to return the count and a merged field for the returned message at the same time. Thank you though, at least I can get an alert out there to start.

jaytech

Ok, so the SQL was even easier than I thought... a simple sub-query on count and problem is solved! Thanks for the solution Petr Vilem

SELECT (SELECT COUNT(*) AS Expr FROM table), someColumn