locked
Notification services and winows service RRS feed

  • Question

  •  Hello,
     
        I'm creating a windows service that listens to a sql server database table each second when any insertion occurs this windows service make a lot of work, to accomplish this task I firstly think in the notification services but it creates an auto generated windows service which I can not edit to do my logic and in the same time it works(the notification services) just like my windows service in listening to the table every Generator Quanta.
       then i think in using the sqlDependency class to listen to the database but it creates a greate load in the machine resources because it exexcutes the sqlcommand continuously even if there is no change in the database
       The question is which is the best implementing my one windows service or using the notification services to send notifications to filesystemwatcher then my service check this file every second?
       i want the database to run the service every change or some changes made in the database

       Thanks
    Tuesday, February 24, 2009 1:49 PM

Answers

  • If I understand your questions correctly, you want to build some kind of system that recognizes when a row has been inserted into a table and then takes some action based on that insertion. Is that right?

    Certainly Notification Services can watch a table, looking for inserts. You can then have it generate notifications, such as smtp, as a result. To write to an separate notification file for each notification will require that you create a custom delivery channel, but that's not difficult.

    Service Broker may also be an option for you.

    And finally triggers may be an option for you as long as the processing you're doing is not too long. Trigger operate in the same transaction that caused them to fire so creating a trigger that takes a while to process (ie writing to a web service, etc) will lengthen the transaction as hence decrease overall performance.

    As a variation on the trigger option, you can have a trigger insert a row into an audit or logging table and then create a separate scheduled job to periodically evaluate the logging table to look for something to do. That's one way to get the long run processing out of the trigger.

    HTH...

    Joe


    Joe Webb, SQL Server MVP | http://weblogs.sqlteam.com/joew/
    • Proposed as answer by Joe Webb Thursday, March 5, 2009 12:52 PM
    • Marked as answer by Joe Webb Monday, April 27, 2009 11:38 AM
    Wednesday, February 25, 2009 12:36 PM