November 20, 2009 at 8:26 pm
I am New to SQL Server Broker Service.
1. How can we create the Message and send it to a Queue, if a record is added or modified in a table.
Like if we have a MemberMaster table, which have a status column. If the Status is modified, we need to send a Queue.
2. We need to Integrate Service Broker and .NET. Can anyone help in giving the overview of the same?
Thanks
Deepak
November 28, 2009 at 2:06 pm
deepakkn (11/20/2009)
1. How can we create the Message and send it to a Queue, if a record is added or modified in a table.
You create a trigger on the table that catches the INSERT or UPDATE and send the new/changed records to the Service attached to your Queue. Here is an example from my presentation "The Top Ten Reasons You Aren't Already Using Service Broker":
--=====================================
--08-3Make SEND into a Trigger
--=====================================
USE [SB_eDrugs]
Print ''
GO
CREATE TRIGGER [dbo].[trgWebUser_SendTo_PayPusher] ON [dbo].[WebUsers]
FOR INSERT AS
BEGIN
/*
Asynchronusly Send New Users info to PayPusher database.
test:
INSERT INTO WebUsers(LogonName,FirstName,LastName,ZipCode,Password)
Select 'User01','Rob','Roberts', 90120,'MyNewPwd'
*/
SET NOCOUNT ON
-- get the number of records inserted.
Declare @recs Int
Select @recs = (Select count(*) from inserted)
IF @recs > 1
BEGIN
RAISERROR('[trgWebUser_SendTo_PayPusher]: cannot handle bulk Inserts!', 17, 1)
ROLLBACK
RETURN
END
--====== SEND the message
-- Get the new User's ID
Declare @user-id as int
Select @user-id = UserID From inserted
-- Start the conversation
Declare @ConvsHdl uniqueidentifier
BEGIN DIALOG @ConvsHdl
FROM SERVICE InitiatorReplyService
TO SERVICE 'PayPusherService'
ON CONTRACT conWebUser
-- Send it & End it
;SEND ON CONVERSATION @ConvsHdl
Message Type msgNewUser
( @userid )
END CONVERSATION @ConvsHdl
--======
END
GO
Of course you will also need to create the queue and any activation procs, and define the message-type, contract and service. You find working examples of all of this in my download kit of this same presentation at my blog site MovingSQL.com, which includes all of the slide and code.
[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 28, 2009 at 2:09 pm
deepakkn (11/20/2009)
2. We need to Integrate Service Broker and .NET. Can anyone help in giving the overview of the same?
It's the same as any other SQL features, the best approach is to just write T-SQL stored procedures that do the actions and functions that you need, and then call them from your .Net code through ADO.Net or Linq.
[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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply