| Author |
Topic |
|
Windza
Yak Posting Veteran
61 Posts |
 Posted - 2011-07-04 : 01:19:27
|
| Hi All, I'd like to know how I can achieve an email alert/notification functionality based on a conditional query or similar. I've looked at data-driven subscriptions (and the workarounds for R2 Standard) but these don't really fit the bill as they're still relying on a schedule (from what I can gather anyway).What I'm hoping to do is send out notifications (in the name of safety) when a value threshold is exceeded so we can investigate or take disciplinary action if requiredAny suggestions?Cheers, |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-04 : 04:37:22
|
| What do you mean by "when a value threshold is exceeded"? How will you know if this value threshold is exceeded? Notifications can be sent using database mail (http://msdn.microsoft.com/en-us/library/ms175887.aspx) by executing i.e the system stores procedure sp_send_mail but there is no magic anywhere...you'll either have to schedule it or execute it from another procedure or something.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
 |
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2011-07-04 : 15:39:39
|
| By 'when a value threshold is exceeded' I'm meaning just that - e.g. set threshold to 5; when data comes in at >5 an email is sent out to various parties... The 'magic' I'm chasing is in the form of either native or 3rd party software that would monitor/review new table data in real-time (as opposed to scheduled) and have email capability. If I have to use a scheduled report than so be it, just seems impractical to schedule something every 5 or so mins to try and achieve pseudo real-time.I'm surprised that this isn't more common... I was really thinking there'd be a host of applications that would call for or already use some form of solution like this? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-04 : 16:15:54
|
quote:
I'm surprised that this isn't more common... I was really thinking there'd be a host of applications that would call for or already use some form of solution like this?
I'm sure there is...maybe I'm not understandig correctly or maybe you're asking the wrong questions. But if it's a monitoring system you're looking for like Nagios or SCOM (Microsoft System Center Operations Manager) they are pretty powerful and do pretty advance monitoring, but as far as I know all the monitoring probes are running on schedules.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2011-07-05 : 01:08:11
|
| It's likely I'm simply asking the wrong questions or not providing enough information... so for clarification;We have a fleet of heavy machinery which is polled in pseudo real-time (<100ms) to collect a fairly comprehensive set of health and performance parameters related to either maintenance or productivity.Some of these parameters are critical to either safety or longevity of the machines and operators (productivity takes a back seat here) and it'd be nice to put this data to good use.For example; some components in the machines are worth $250-500K and a single parameter such as oil pressure or level can mean the difference between changing a $1500 pump or a $300K engine... there are onboard systems in place to prevent some of these events occurring but there are still a host of parameters that it would be nice to receive some form of relatively urgent warning about.I understand that I can schedule a data-driven report to monitor a parameter every 30s-1min (which is what I'm currently doing) but I'm not sure if this is really the intended use of data-driven reports and I'm concerned there may be performance implications (happy to be corrected on this)... from what I understand of dd reports, they're primarily aimed at being able to provide flexible/dynamic content rather than instantaneous reporting.It just seems to me that there would be purpose built software out there to monitor SQL content (as opposed to server and network performance) and provides features like SMS, pager or email notification... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-05 : 02:33:50
|
| I have no experience in the heavy machinery area or the kinds of environments you are working in so please forgive any ignorance or failure to give the proper advice. But there are many ways to trigger an event in a database, it just depends in what you need to be done and when. If you have data written to a sql server database table you can put a trigger on this table which would be as real-time as it gets. A trigger fires for every single batch of insert/update/delete (DML) operation and is the closest thing you can get to real-time in sql server without modifying the DML procedures. The problem however with triggers is that it runs in the same scope as the DML procedure which in essence means that each insert/update/delete will take longer. Another alternative is to use the Service Broker which is asynchronous message based processing (create each message in a trigger perhaps?), and then you have sql server agent which is scheduled processing. You also have the possibility of creating a custom Windows Service that runs continuously or you can more or less do the same with sql server agent as well, building an eternal loop that does some checking, maybe waits for a few seconds and then runs again. There are many ways to build monitoring, it's just a matter of finding out which method suits you best.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2011-07-11 : 20:43:53
|
| Lumbargo... SQL 'Triggers' are EXACTLY what I am chasing - thank you!I just can't fathom why I never saw any reference to them in all my googling (and trust me, I burnt through a whole lot of keywords - clearly 'triggered' wasn't one of them).I did a little background into the performance implications associated with triggers and in our application there is nothing notable that I'm concerned about... all I'll be doing is initiating a subscription/job based on an '=' condition (no select statements involved).Anyway, I genuinely will benefit from you having taken the time to respond so thanks again...Cheers |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-07-11 : 20:58:13
|
| I support a heavy machinery system that includes sensors like you've mentioned. Our product is only available in the U.S. and Europe as far as I know, so we wouldn't be considered a competitor. We don't handle our sensors through triggers. We handle it at the actual device that is reading the sensors as well as at the application layer, which is responsible for sending emails (sometimes to a pager device if the sensor is critical enough).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2011-07-12 : 00:18:47
|
quote:
Originally posted by tkizer
We don't handle our sensors through triggers. We handle it at the actual device that is reading the sensors as well as at the application layer, which is responsible for sending emails (sometimes to a pager device if the sensor is critical enough).
Tara, that'd make a lot more sense but the system in place is retailed and managed independently so I don't have the authority (or the facility) to implement those sort of measures... Thankfully though, I do have ownership of the SQL Server on which it all runs and now that I'm 'trigger' happy I have the functionality I was looking for.I'd be interested in knowing a little more about the product you're describing... at this point in time, we're by no means committed to our current supplier as the systems is in trialing stages and I'm always on the lookout for alternative options.Feel free to email me with any information you're able/willing to pass on.Cheers |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
MSSQL 2008 R2 Alerts/Notification Options...
Tara Kizer