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

Question

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)?

Answer 1

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.

Answer 2

How would you go about channeling such an event to be used by VDF.

How you'd channel to VDF depends on whether you want something visible happening inside your VDF application or whethor you just want to trigger a procedure in VDF.

If the latter, I'd suggest you use a Webapp to provide this functionality. VDF (versions over about 10) have web development built in, and it's simple to provide a SOAP interface to do whatever you want.

{ Published = True  }
{ Description = "Test the service is alive - returns 'Hello World'"  }
Function HelloWorld Returns String
    Function_Return "Hello World"
End_Function

If the former, then don't be worried about using a timer object to poll the database every couple of seconds or so. You'll find the code is more "dataflex" and there's only a negligible perfomance hit.

There is however an open source VDF library for MSMQ, and that can be found at ..
http://www.vdf-guidance.com/ContribPage.asp?Page=PKGGENMESSAGING&ContribRecId=106

Finally - The Data Access newsgroups are the best place to go for Visual Dataflex support. Use http://sture.dk/wasp to search historical newgroups and go to news.dataaccess.com (using an NNTP client of your choice) to post and answer questions.