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
7/01/2006
6/09/2006
Replication troubleshooting
If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn't giving you the details you need, turn on logging.
This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.
To log an agent's activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the "Run Agent" step.
You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3
Change the path and file name of the first one to an appropriate drive on the Distribution server.
The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.
If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.
Good luck, and happy replicating!
Kevin3NF
If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn't giving you the details you need, turn on logging.
This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.
To log an agent's activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the "Run Agent" step.
You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3
Change the path and file name of the first one to an appropriate drive on the Distribution server.
The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.
If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.
Good luck, and happy replicating!
Kevin3NF
6/06/2006
Bike stuff
Had a great ride Saturday...19.2 mph with the Richardson Bike Mart 34 mile group. Stayed with the main bunch the whole time...never done that before.
Happy Birthday Kaitlyn!!!
Had a great ride Saturday...19.2 mph with the Richardson Bike Mart 34 mile group. Stayed with the main bunch the whole time...never done that before.
Happy Birthday Kaitlyn!!!
5/23/2006
5/19/2006
5/08/2006
4/29/2006
4/28/2006
So a customer asked me yesterday:
"How can I search all my DTS packages in SQL 2000 for certain field/table names? Peoplesoft patches sometimes change these at the schema level."
Answer: You can't, using any native SQL Server utility. The best you can do is save the DTS packages as a Visual Basic File (.bas or .txt) and either aggregate them or index them via some other utility.
Alternative Answer: DTS Compare from www.red-gate.com might do it, but I have not tested it.
Happy searching!
Kevin3NF
"How can I search all my DTS packages in SQL 2000 for certain field/table names? Peoplesoft patches sometimes change these at the schema level."
Answer: You can't, using any native SQL Server utility. The best you can do is save the DTS packages as a Visual Basic File (.bas or .txt) and either aggregate them or index them via some other utility.
Alternative Answer: DTS Compare from www.red-gate.com might do it, but I have not tested it.
Happy searching!
Kevin3NF
4/27/2006
Interesting issue I'm working on...
Company with SQL Servers all over the world applies MS06-014 and SQL Server 2000 SP4 close together.
Now none of their applications can make a TCP connection. Named Pipes is fine, but the application requires TCP.
Did all the normal stuff like checked the Server Network Utility (SNU), checked the firewalls to ensure the appropriate ports were open, explicity specified tcp in the servername (tcp:servername, port). No go.
Duh moment...check the SQL Server ERRORLOG. Nope...SQL Server was not listening on TCP, even though the SNU had it enabled. No errors in the log, or in the application log...just not listening on that protocol.
Resolution:
Check the value of the tcpport parameter in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
In this instance, it was NULL. SNU was reporting 1433. Apparently SNU caches the port, or if the registry is NULL, it uses a default value to fill in the blank.
So we set the value manually, and all was good.
Lesson learned:
Check the ERRORLOG earlier in the process, and never trust a GUI.
Kevin3NF
Company with SQL Servers all over the world applies MS06-014 and SQL Server 2000 SP4 close together.
Now none of their applications can make a TCP connection. Named Pipes is fine, but the application requires TCP.
Did all the normal stuff like checked the Server Network Utility (SNU), checked the firewalls to ensure the appropriate ports were open, explicity specified tcp in the servername (tcp:servername, port). No go.
Duh moment...check the SQL Server ERRORLOG. Nope...SQL Server was not listening on TCP, even though the SNU had it enabled. No errors in the log, or in the application log...just not listening on that protocol.
Resolution:
Check the value of the tcpport parameter in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
In this instance, it was NULL. SNU was reporting 1433. Apparently SNU caches the port, or if the registry is NULL, it uses a default value to fill in the blank.
So we set the value manually, and all was good.
Lesson learned:
Check the ERRORLOG earlier in the process, and never trust a GUI.
Kevin3NF
4/26/2006
I got a call today from a customer that wanted to upgrade SQL Server 6.0 to SQL 2000.
Problem...old tired server that may not survive a reboot, and no direct upgrade path.
Solution:
BCP the data out to text file, and find some 3rd party utility that can reverse engineer the schema.
We agreed to speak no more of this need. :-)
Problem...old tired server that may not survive a reboot, and no direct upgrade path.
Solution:
BCP the data out to text file, and find some 3rd party utility that can reverse engineer the schema.
We agreed to speak no more of this need. :-)
So...2.5 years after I started this, its time to add a second post :-)
This for now, is going to be a day-to-day account of various things I run across in my professional life as a SQL Server DBA, IT guy, Consulting firm owner, etc. I may toss in some cycling related items from time to time, just because I can.
I am currently working in my second contract for Microsoft SQL Server Support, on the Premier Integration team. My team handles issues such as Clustering, Replication, DTS, Security, Connectivity and BCP. Currently, I only deal with SQL Server 2000 and 7.0. No 2005 for the contractors.
Hope you like databases :)
Kevin3NF
This for now, is going to be a day-to-day account of various things I run across in my professional life as a SQL Server DBA, IT guy, Consulting firm owner, etc. I may toss in some cycling related items from time to time, just because I can.
I am currently working in my second contract for Microsoft SQL Server Support, on the Premier Integration team. My team handles issues such as Clustering, Replication, DTS, Security, Connectivity and BCP. Currently, I only deal with SQL Server 2000 and 7.0. No 2005 for the contractors.
Hope you like databases :)
Kevin3NF
Subscribe to:
Posts (Atom)