We are looking to create an email notification system using our issue tracking software and the SQL Server database. The goal is to send email when a number of conditions are met.

This is to satisfy custom notifications that our customers request. For example, one customer would like an email sent to person Y if a ticket is opened for a software problem, and an email sent to person Z if a ticket is opened for a hardware problem.

SQL Server Notification Services seems to have the functionality, but we are interested in a GUI rather than writing queries by hand each time. We also need some way to manage them, since the number of custom notifications could get into the thousands.

EDIT:

These are good suggestions, but one requirement is that we have a GUI for managing rules. We don't want to be hand-writing queries every time we need to create or change a notification.

We know we could hack something together with scripts, triggers, and scheduled tasks, but it seems like there ought to be some kind of packaged product that does this already. Maybe it requires some database skills to initially set up, but ideally after that it can be updated by managers who aren't DBAs.

We already have an issue system. The notifier doesn't need to integrate it, the idea we have is just to have the notifier check the tables we tell it and send messages if they return any rows.
–
Stephen JenningsJun 2 '09 at 22:01

Then I'd recommend you just get a developer and build something that uses xp_sendmail based on rows returned. Anything you buy will require integration and development time anyway. This isn't that complicated, should be a few days with admin tools included.
–
Steve JonesJun 3 '09 at 16:15

Search open source sites. You might stumble upon an app you can build upon.
–
MarlonRibunalJun 4 '09 at 15:33

We considered this, but managing that many triggers would be infeasable. We don't want to muck around with the database directly to add or change these notifications.
–
Stephen JenningsJun 3 '09 at 0:50

Stephen, this sounds more like an outline spec for an application rather than a 'how do I do this ...?'! Do the rules change so frequently that Notification Services isnt the best solution?

Reporting services can be scheduled and it is possible to use it to execute INSERT, UPDATE and DELETE commands based on user supplied parameter values as well as SELECT but that is not a polished way of running what seems to amount to a small CRM GUI that you have outlined.

Maybe looking for an ASP.Net developer to build you a GUI around the rules you have detailed would be your way forward if you dont have time/experience to 'roll your own'?

So, you need something scalable. Why not create a table that you can insert rules into, first_name, Last_name, email_addr, table OR table.column such that you add your email rules to this table. Then run a scheduled SQL script that performs a generic query by consulting the rules table, and checks the database for any rule matches. By scripting it, you control how often the job runs, and also control how it emails, i.e. if it was a Perl Script using DBI database calls, you could also use a Perl Sendmail module to issue the emails right from within the same script.

You could even start with just a flat text file, or XML file with the user-based rules...

If this is not inherently supported in the product, that you'd want to write an automation script that can be scheduled at your discretion. The other option is possibly a hybrid of this, and use a database trigger ON INSERT to then fire the script/sql that performs the checks and emails.

Simply wrap this process with a simple web front-end to add your rules. The SQL would largely stay the same and be generic. You would just need a form to INSERT/DELETE/UPDATE the rows of rules.