Raise Event from store procedure to Windows Service

Question

Hi

Is it possible to trigger an action in Windows Service, or is it possible to raise an event that can be caught in a Service from a stored procedure.

If you imagine a service runs every 5 minutes thats performs some action. What if something happens in a database or a stored procedure is executed and I want to trigger this action earlier than the 5 minutes.

Does anybody have any ideas?

Thanks

Ed

Answer 1

You can create a CLR procedure in SQL Server that somehow communicates with the service using SOAP, Remoting, or whatever medium. This is generic though. I think query notification feature of SQL Server 2005 can be helpful here.

Answer 2

if you do a raiserror(@ErrorMessage,16,1) it should generate an exception on the client side as long as you use a severity and state of 16 and 1.

Answer 3

It sounds like you shouldn't be using a windows service at all. I'd setup a SQL job to run every 5 minutes, then setup a trigger or whatever you expect to happen to run the job manually if necessary.

EDIT: However, if the service is doing something outside of the database, you can use xp_cmdshell to run system commands (not the best option though).

Answer 4

Following on from Mehrdad's comments, I was able to solve the problem using the SqlDependancy Class in the .NET framework.

This Class allows you to register a SQL query and connection with it, when the result of that query changes an event is raised to indicate a change in the data.

This allowed me to catch data changes almost instantaneously and deal with appropriately.

In order to use this Class you will need to Enable Query Notifications on the database you wish to query see: Enabling Query Notifications

A good example of how to use this class in a Windows Application is found here.