Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

monitor table and invoke stored procedure if any inserts happens in that table using notification service Expand / Collapse
Author
Message
Posted Wednesday, November 05, 2008 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 05, 2008 10:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:50 PM
Points: 2,897, Visits: 5,966
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
Posted Wednesday, November 05, 2008 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 05, 2008 11:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 05, 2008 11:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
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.


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
Posted Wednesday, November 05, 2008 11:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 05, 2008 11:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
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.




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 #597594
Posted Wednesday, November 05, 2008 11:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 05, 2008 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
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.


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 #597609
Posted Wednesday, November 05, 2008 7:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse