How to use SQLServer Service Broker/SQLNotifications with Delphi7?

Question

delphi 7 and mssql 2008 are requirements I can't change for a new project I'm starting to work and if possible I'd like to get notifications/events if data changes on the server so I don't have to poll so the question is:

is it possible to utilize SQLService broker with D7 or is there something similar (was reading/thinking about crosstalk and ado.net but support starts only at D2007)?

Update: Just to be absolutely clear, I want to be able to get an Event if data changes in certain tables (eg. insert/updates on specific tables) and not if a table/database changes.

Yolandayolande

Answer 1

Yes, is possible use delphi to access to the notifications and Events exposed by the MSSQL Service Broker. To do this you can use the WMI Provider for Server Events which exposes a very rich set of WMi events and classes. If you are new to the WMI, I recommend you which read these articles Accesing the WMI from Object Pascal Code and Delphi and WMI Events also you can use the WMI Delphi Code Creator to create an Delphi snippet to access the events of the MSSQL Service Broker.

Cider

hi, after some reading as much as I could, and from what I understand now, it looks to me that with WMI Provider you can't get an even if data changes on a specific table (eg. update tab1 set mod_date = getdate() where id = 1) but only if tables change (eg. alter tab1 add newcol int). – Yolandayolande

how should I proceed with this question - vote for close, delete or? – Yolandayolande

Sorry for the delayed answer. You can use the the WMI provider for server events not just for monitor DDL sentences, also you can monitor Trace Events (sql senteces which includes SELECT, INSERT UPDATES and so on) Check the MSDN Documentation for WMI Provider for Server Events Classes and Properties – Cider

thx - a lot has happened since I've asked about it (got a new job etc..). If I find a quiet moment I'll have a look again. Thanks. – Yolandayolande