December 30, 2008 at 10:46 am
Hi all,
I have a stored Procedure called myprocess_SP which will be processing 1.5 million records in a table. In this process I need to log the present value and oldvalue of each and every record if it is updated,deleted,inserted into some other 'ABC' database tables.
I actually wrote a 'backuplog_SP' procedure which is called in myprocess_SP were I will be passing the oldvalue and newvalue as parameters if it is a updatable record or I will just pass the newvalue itself it is a new record and that logs into the 'ABC' database.
Now instead of calling the backuplog_SP I wanted to use the SQL Server Service Broker feature. I am not in a position to decide how service broker can be helpful in this scenario.
Please suggest me how to use service broker in this scenario.
Thanks,
blnbmv
December 30, 2008 at 11:01 am
Create an SB queue (and service, message_type and contract to go along with it). Write a new procedure to send the changes (old & new values as XML) to the queue. Write an activation procedure on the queue to receive these messages, shred the XML and pass the data/info to your old procedure "myprocess_SP". Change "myprocess_SP" to have the right parameters and work within this implementation.
[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]
December 30, 2008 at 11:03 am
Also, see my Code Camp presentation "The Top 10 Reasons You aren't Already Using Service Broker" and associated code here: http://www.movingsql.com/dnn/Portals/4/Materials.zip
[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]
December 30, 2008 at 11:08 am
Hi,
Thanks for your response. I would like to know is there any scenario where I can look that example.
Thanks,
blnbmv
December 30, 2008 at 11:31 am
The code example in my presentation demonstrates how to do this from a trigger.
[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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply