What would be a best way to notify an application (C++ or Java) when underlying DB changes?

Question

I'm writing a C++ console application that needs to respond to a change in a specific field of a database table. Though I can keep sending a query to check the field periodically, I'd like to avoid this because multiple instances of this application might be running. I have heard of the Query Notification feature of SQL Server 2005 but it seems that the feature would not avaialbe for my setting (SQL Server 2000). In this case what would be a best way to achieve this functionality?

Answer 1

I think you should use triggers for that.

When table field gets changed trigger is fired and notify other application using any IPC mechanism or writing necessary information in registry or temporary files .

Answer 2

I think the Notification Services itself can be run on SQL Server 2000.

Take a look at these articles:

• http://support.microsoft.com/kb/822348
• http://www.eggheadcafe.com/articles/20021122.asp

Hope this helps.

Answer 3

Here's how I would do it.

You have your console application which is repsonsible for checking when the value of a specific column of a table changes anywhere in that column (update, deletes, inserts, whatever).

Step 1 - don't point that console application at the DB, create a MSMQ and make it look there

Step 2 - create a second console application whose sole job it is is to write a value to the above MSMQ

Step 3 - Create a trigger on the table in question to execute the console application from step 2.

Answer 4

You can only poll for changes periodically. There is absolutely no way to get notifications from SQL Server 2000. All claims to the contrary are pipe dreams.

Triggers that do any sort of external notification, via a OLE automation (sp_oa methods), via extended procedures, via xp_cmdshell or anything like are not going to work. First there is the issue of performance: the last thing you want your database to do is to block in triggers waiting for external processes to respond. Second, there is this usually ignored little detail of correctness in a transactional environment: any notification sent during a trigger would have to be 'unsent' if the enclosing transaction is rolled back. The only way to achieve this is enroling in a distributed transaction with the notification mechanism (MSMQ for instance) and this means a drop in troughput from hundreds/thousands per second to single digits.

Don't go there. Upgrade to SQL 2005 and use QN, which is the only active change notification mechanism. Or settle for periodic polling of last changes.