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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy