monitor table and invoke stored procedure if any inserts happens in that table using notification service

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » monitor table and invoke stored procedure if...

15 posts, Page 1 of 2

1 2 »»

monitor table and invoke stored procedure if any inserts happens in that table using notification service

Rate Topic

Display Mode

Topic Options

Author

Message

barathvaj.s

Posted Wednesday, November 05, 2008 10:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

i have a one source table,in which data is inserted when the feed is over. my job is to monitor that table,if any new record inserted i need to call the stored procedure which has some business logic to it..

I suggested the asynchronous trigger using service broker to my project manager. but he need that to be done with the sql server notification service.
i don't know how to map this scenario with notification service. i need a quick solution ya....

please can any one help as soon as possible.

Post #597567

Luke L

Posted Wednesday, November 05, 2008 10:56 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:42 AM
Points: 2,891, Visits: 5,858

Why not just create an After Insert trigger directly on the table. Why even deal with Service Broker?

http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

-Luke.

To help us help you read this

For better help with performance problems please read this

Post #597570

barathvaj.s

Posted Wednesday, November 05, 2008 11:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

Thanks for the reply Luke, they not ready to use triggers at all because the logic is very large based on the each record insertion,

tats what i suggested asynchronous trigger to separate a process in to different transaction..

That too the client is not ready to accept, he need this scenario to be implemented only by notification service .

Post #597578

barathvaj.s

Posted Wednesday, November 05, 2008 11:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

can anybody able to find out the answer..:)

Post #597585

Lynn Pettis

Posted Wednesday, November 05, 2008 11:18 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

You are aware that SQL Server Notification Services (if that is what you are refering to in your posts regarding notification services) goes away with SQL Server 2008.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #597586

barathvaj.s

Posted Wednesday, November 05, 2008 11:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

Thanks for the reply...

Yes the notification service going to be removed from 2008.

but the client is strictly sticking to the notification service

is there a way to achieve this using notification service

Post #597589

Lynn Pettis

Posted Wednesday, November 05, 2008 11:32 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

All I can say, is maybe. When I found out that NS would not be a part of SQL Server 2008, I stopped even looking at it for anything. Started concentrating more on Service Broker. It looks like it will be here for the long haul.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #597594

barathvaj.s

Posted Wednesday, November 05, 2008 11:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

Thanks for the reply ya.......

is there any alternatives to trigger, to call the stored procedure when the insert happens in the table. because they don't need trigger

Post #597595

Lynn Pettis

Posted Wednesday, November 05, 2008 11:57 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

The best alternative you have, you've been told you can't use. This is a perfect place to use SQL Server Service Broker. A trigger sends a message to a queue to start processing indicating what record (or records) need to be processed. This is a scalable solution, and you can control how many instances of the stored proc are actually being run at any given time.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #597609

barathvaj.s

Posted Wednesday, November 05, 2008 7:18 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 12, 2010 6:27 AM
Points: 37, Visits: 56

Reason for avoiding trigger even though it is scalable with service broker , the source table is in the one database which is designed by other parties . we don't want to make changes by adding trigger to that database.. tat is one of the reason.

client ask us to work this scenario with both notification service and service broker..

The job of the notification service is to notify by calling stored procedure when any inserts happens in that table.

stored procedure will place the message in the service broker queue by opening up a dialog session. the activated queue stored procedure will receive the message body from queue and do the business logic..

another doubt in the asynchronous trigger , say there is a bulk insertion happening to the table ,therefore the service broker will open a new dialog session for each insertion to the table .

therefore it open up a multiple instances of stored procedure , i have doubt in this scenario , is the stored procedure execute in parallel fashion.

i need that to be executed one by one ... that is sequential..

can we do with max_queue_readers or some other one?

can any one clarify it......

Post #597810

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions