SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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.
Luke L
Luke L
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4819 Visits: 6136
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
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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 .
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 56
can anybody able to find out the answer..Smile
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50975 Visits: 38675
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)
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50975 Visits: 38675
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

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)
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50975 Visits: 38675
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

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)
barathvaj.s
barathvaj.s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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......
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search