sql server programming: Service Broker Question -- sql server programming

Re: Service Broker Question

Roger Wolter[MSFT]
3/31/2007 5:51:12 PM

sql server programming: Do a BEGIN TRANSACTION before doing the receive and then don't commit the
transaction until the mail is sent successfully. A RECEIVE command is
actually a delete so if the transaction commits the message is gone. That's
the way error handling works in all transactional messaging.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

[quoted text, click to view]

Service Broker Question

scott ocamb
3/31/2007 6:28:16 PM

I am building a service broker implementation.
Our website allows a user to select a bunch of emails to send. These emails
are send to a service broker queue.

The receive queue executes an extended stored procedure written in C#. This
proc performs mail merge work on the body and sends the email.

I have noticed that when an error occurs, the item is no longer in the
service broker queue. This is not good of course. I was expecting some sort
of error or retry queue like biztalk has.

so..

my question is how can i recover form errors and not loose items that have
been queued up.

scott

Re: Service Broker Question

Remus Rusanu [MSFT]
3/31/2007 7:28:23 PM

BEGIN TRANSACTION;

[quoted text, click to view]

COMMIT;

This would be the minimum needed. If you'll find the the long held
transactions while you do the C# processing starts causing problems, there
are some trick you can try by using conversation timers.

HTH,
~ Remus

Re: Service Broker Question

Tom Moreau
3/31/2007 8:21:59 PM

Consider beginning a transaction, attempting "the work", if "the work"
fails, rolling back the transaction and sending an error message back to the
initiator?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
I am building a service broker implementation.
Our website allows a user to select a bunch of emails to send. These emails
are send to a service broker queue.

The receive queue executes an extended stored procedure written in C#. This
proc performs mail merge work on the body and sends the email.

I have noticed that when an error occurs, the item is no longer in the
service broker queue. This is not good of course. I was expecting some sort
of error or retry queue like biztalk has.

so..

my question is how can i recover form errors and not loose items that have
been queued up.

scott

Re: Service Broker Question

Roger Wolter[MSFT]
3/31/2007 9:10:14 PM

I'm not sure I understand your activation proc - it looks like you're
beginning the dialog sending and receiving in the same proc? I'm not sure
how that work - where do the messages come from that activate the procedure?

Anyway, for the receive part put a BEGIN TRANSACTION before the RECEIVE, and
if the mail is sent correctly do the END CONVERSATION and COMMIT. If there
is an error in the mail sending, do a ROLLBACK.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

[quoted text, click to view]

Re: Service Broker Question

scott ocamb
3/31/2007 9:54:54 PM

thanks for your response.

Any help would be appreciated....

I do this to set the activation proc.

ALTER QUEUE [KS_EmailRecieveQueue] WITH ACTIVATION
( STATUS = ON, MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = KS_Recieve_EmailMessage_FromQueue, EXECUTE AS OWNER)

here is the activation proc.

DECLARE @conversation_handle UNIQUEIDENTIFIER,
@message_body XML,
@message_type_name NVARCHAR(128);
DECLARE @msg NVARCHAR(128)
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE KS_EmailSendService
TO SERVICE 'KS_EmailRecieveService'
ON CONTRACT KS_EmailContract
WITH ENCRYPTION=OFF;

-- Tran in here somewhere?????

RECEIVE TOP(1)
@conversation_handle = conversation_handle,
@message_type_name = message_type_name,
@message_body = message_body
FROM [dbo].[KS_EmailRecieveQueue]

IF @message_type_name = 'KS_EmailMessage'
BEGIN
SELECT @message_body AS MESSAGE
SET @msg = CONVERT(nvarchar(Max), @message_body)
select @MSG

-- THIS IS A C# METHOD

exec KS_BULKMAIL_SP @msg

END
END CONVERSATION @conversation_handle WITH CLEANUP ;

Thanks for your help!!!!!

[quoted text, click to view]

Re: Service Broker Question

Tibor Karaszi
4/1/2007 12:00:00 AM

....and consider having some retry logic in there (try some 10 times for a certain email, then log to
some table and commit). If the mail fails over and over again, you will essentially have an endless
loop, since SB delivers messages in order.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Re: Service Broker Question

Tom Moreau
4/1/2007 8:15:13 AM

Also, consider putting this all in a TRY CATCH block.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OE44WpCdHHA.3960@TK2MSFTNGP04.phx.gbl...
....and consider having some retry logic in there (try some 10 times for a
certain email, then log to
some table and commit). If the mail fails over and over again, you will
essentially have an endless
loop, since SB delivers messages in order.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.