Notify a C++ application when a change in a SQL Server table is made

Question

I've posted this before but haven't obtained a suitable answer that fits my requirements. I'm looking for a technology to notify a C++ application when a change to a SQL Server table is made. Our middle-tier is C++ and we're not looking to move onto .NET infrastructure which means we can't use SQLDependency, or SQL Notification Servers. We're also stuck with SQL Server 2005 for the time being which also eliminates SQL Service Broker External Activation (that is introduced in SQL 2008).

To give a broader understanding of what I'm trying to achieve: our database is being updated with new information; Whenever a new piece of information is received, we'd like to push this to the C++ application so that its dashboard reflects up-to-date data for the user.

We know we can do this by having the C++ application polling the database but I see this as inefficient architecture and would like to have SQL push the information or a notification to C++.

Answer 1

I would suggest writing a SQL CLR trigger that uses Net Pipes to notify you app.

HTH

Answer 2

You can actually use Query Notifications from C++. Both the OleDB and the ODBC clients for SQLNCLI10 and SQLNCLI providers support Query Notifications. See Working with Query Notifications, at the second half of the page you'll find the SSPROP_QP_NOTIFICATION... stuff for the OleDB rowsets and the SQL_SOPT_SS_QUERYNOTIFICATION... stuff for ODBC statements. So subscribing for notifications from a C++ mid tier is absolutely doable. And the second piece of the puzzle is to actually get the notifications, which is nothing else than posting a RECEIVE and waiting. In other words you can roll your own SqlDepdency in pure C++ over OleDB or ODBC. Once you have the mid-tier notified, it's a piece of cake (well, sort of) to update the client displays.

Between all the alternatives to detect data changes, you won't find anything better than Query Notifications.

BTW, one thing you should absolutely avoid is notifying the clients from triggers (oh, the horror...).

Answer 3

There's another suggestion, and it may be simpler, you may deem this suggestion as rubbish because of the long-winded way of doing it, why not send an email on a trigger notification of the table such as insert/delete/update to a private email address with a subject line of 'DATA CHANGE NOTIFY', your C++ application can periodically poll via POP3 to check emails from the private email address... Mercury pop3 boxes can be used on the server (it's part of the xampp application) just a thought...

Hope this helps, Best regards, Tom.