Notify C ++ application when changing SQL Server table

Question

I posted this before, but didn't get a suitable answer that fits my requirements. I am looking for technology to notify a C ++ application when a SQL Server table is modified. Our middle tier is C ++, and we don’t want to switch to the .NET infrastructure, which means that we cannot use SQLDependency or SQL Notification Servers. At the moment, we also adhere to SQL Server 2005, which also eliminates the external activation of SQL Service Broker (which is implemented in SQL 2008).

To give a more complete picture of what I'm trying to achieve: our database is updated with new information; Whenever a new piece of information is received, we would like to click this on the C ++ application so that its toolbar displays the updated data for the user.

We know that we can do this if a C ++ application polls the database, but I see it as an inefficient architecture and would like SQL to push information or notification in C ++.

Answer 1

In fact, you can use request notifications with C ++. Both OleDB and ODBC clients for SQLNCLI10 and SQLNCLI providers support query notifications. See Working with query notifications , in the second half of the page you will find material SSPROP_QP_NOTIFICATION... for OleDB and SQL_SOPT_SS_QUERYNOTIFICATION... material for ODBC statements. Thus, subscribing to notifications from mid-level C ++ is absolutely doable. And the second part of the puzzle is to actually receive notifications, which is nothing more than a RECEIVE publication and an expectation. In other words, you can flip your own SqlDepdency in pure C ++ on top of OleDB or ODBC. Once you get a mid-level notification, this is a piece of cake (well, sort of) to refresh client displays.

Between all alternatives for detecting data changes, you will not find anything better than query notifications.

By the way, you should absolutely avoid notifying clients from triggers (oh, horror ...).

Answer 2

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

NTN

Answer 3

There is another suggestion, and it could be simpler, you can consider this proposal garbage because of the long way to do it, why not send an email to notify the trigger about a table like insert / delete / update to a private email address with a subject "DATA CHANGE NOTIFY", your C ++ application can periodically poll through POP3 to check emails from a private email address ... Mercury pop3 fields can be used on the server (this is part of the xampp application) just a thought ...

Hope this helps, Regards, Tom.