SQL Server 2000 Notification Services
OK folks....it seems a lot of people are scared of implementing and troubleshooting SQL 2000 Notification Services. Perhaps its thought of as something complicated like replication can be?
I'd like your thoughts on this
My take on it (which combined with $.35 will get you a phone call) is that this is a very simple process:
1. Something happens in your database (add a row, sell a stock, whatever)
2. Information about that event moves to the Events table of the separate NS database
3. Periodically, a query runs to see if anyone cares about that Event (i.e. has Subscribed to it)
4. If so, a record for each match of Event and Subscriber goes into the Notification table
5. Notification gets Delivered (via SMTP,phone,etc.)
That's it.
Troubleshooting:
Most common complaint: "I didn't get notified when Oracle stock dropped $20..."
1. Look in the Application and System Event logs on the SQL Server running the NS database for errors
2. Look in the Notifications table to see if there are rows there that match. If so, your delivery system is hosed
3. If not, look for the event in the EVENTS table. If not there, you will NOT get notified. If they are, see if you have a valid Subscription to the Event (check the appADF.xml file for the query that does this...run it)
4. If the Events are not in the Events table, you need to look at whatever process is in place to move data from your source database to the NS database. This could be triggers, stored procs, custom DLL/EXE....just about anything.
Note for MOM 2005 users: If you see events in the MOM console but not in the Events table...good luck figuring out why. Try re-starting the service (Notification Workflow I think...). This is a black box that not even Microsoft understands really well, yet.
Final note...NS databases are SQL databases just like any other...please maintain your indexes and vacuum your old useless data (see NS Books Online for vacuuming setup)
Please add comments as necessary...I've tried to "English" this a bit for clarity.
Kevin3NF
