Search code examples
sql-servercomactivexdataflex

Receiving automatic notification from SQL Server when a table is non-empty

I would like to use an SQL Server table as an action queue. So whenever the table is non-empty, I'd like some sort of notification to my VDF application, in order to avoid constantly polling the database. VDF relies on using stdcall, the standard calling convention for the Win 32 API, or COM/ActiveX. Another solution that could work for me is to simply run an exe file when the table is non-empty.

Are there any events in SQL Server that could be used here? And how would you go about channeling such an event to be used by VDF (as described above)?

Solution

  • You could use an ON INSERT trigger, possibly to send the row right to MSMQ, where your application listens for events as new messages come in.

    Here is an example of how to feed MSMQ using SQL Server, and here a short one on how to listen for events.

    There also is the SQL Server Event Provider, which basically just does the polling for you.