November 5, 2008 at 10:46 am
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.
November 5, 2008 at 10:56 am
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.
November 5, 2008 at 11:06 am
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 .
November 5, 2008 at 11:17 am
can anybody able to find out the answer..:)
November 5, 2008 at 11:18 am
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.
November 5, 2008 at 11:24 am
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
November 5, 2008 at 11:32 am
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.
November 5, 2008 at 11:38 am
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
November 5, 2008 at 11:57 am
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.
November 5, 2008 at 7:18 pm
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......
November 5, 2008 at 8:05 pm
barathvaj.s
READ the article whose link is in Lynn Pettis signature block and give us some more information so that we may help you. For example how is the data imported into the database, how often ... etc., etc.
November 5, 2008 at 8:06 pm
November 5, 2008 at 8:31 pm
I think that there might be some confusion here between two very different things.
As far as I know, Notification Services (NS), is a message distribution facility in SQL server 2005 that gets folded into Reporting Services (RS) in SQL Server 2008. It is intended for the duplication and distribution of huge volumes of messages to hundreds, thousands or even millions of targets. This is really an application level facility meant to deal at a relatively high-level.
On the other hand, mere "notifications" are part of the Data Engine, come in two flavors (Query notifications and Event notifications) and are intended to provide an asynchronous message signaling facility. This is a code level facility meant to deal efficiently with a single message at a time at a very low level.
For your purposes, I think that what you want is a Query notification on a query similar to this:
Select BIG_COUNT(1) From MyTable Group By something
By the way, let your boss know that Query & Event notifications work by using Service Broker for their message delivery and procedure activation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 5, 2008 at 10:23 pm
Thank you for the reply mate ......................
Can u tell the best site for query notification with service broker that would great for me ..... 🙂
November 5, 2008 at 10:48 pm
Well fof Service Broker, you can start at my blog: download the zip of my code camp presentation on Service Broker. Also, some of the links ar eto blogs on Service Broker. Query notifications on the ohter hand, I have not seen must of anything outside of Books Online. Please let me know if you find anything there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply