Asp Forum - Service Broker Question - microsoft.public.sqlserver.programming

scott ocamb

3/31/2007 10:28:00 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

7 Answers

Tom Moreau

4/1/2007 12:22:00 AM

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
.
"scott ocamb" <socamb@kiinc.com> wrote in message
news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
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

Roger Wolter[MSFT]

4/1/2007 12:51:00 AM

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/cp...

"scott ocamb" <socamb@kiinc.com> wrote in message
news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
>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
>

scott ocamb

4/1/2007 1:55:00 AM

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!!!!!

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:e4AiZf$cHHA.4632@TK2MSFTNGP03.phx.gbl...
> 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/cp...
>
> "scott ocamb" <socamb@kiinc.com> wrote in message
> news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
>>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
>>
>

Remus Rusanu [MSFT]

4/1/2007 2:28:00 AM

BEGIN TRANSACTION;

> 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 ;

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

Roger Wolter[MSFT]

4/1/2007 4:10:00 AM

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/cp...

"scott ocamb" <socamb@kiinc.com> wrote in message
news:u8gNMEAdHHA.3648@TK2MSFTNGP05.phx.gbl...
> 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!!!!!
>
>
> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
> news:e4AiZf$cHHA.4632@TK2MSFTNGP03.phx.gbl...
>> 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/cp...
>>
>> "scott ocamb" <socamb@kiinc.com> wrote in message
>> news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
>>>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
>>>
>>
>
>

Tibor Karaszi

4/1/2007 6:53: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/d...
http://sqlblog.com/blogs/tib...

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:O17ToOBdHHA.4468@TK2MSFTNGP03.phx.gbl...
> 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/cp...
>
> "scott ocamb" <socamb@kiinc.com> wrote in message news:u8gNMEAdHHA.3648@TK2MSFTNGP05.phx.gbl...
>> 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!!!!!
>>
>>
>> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
>> news:e4AiZf$cHHA.4632@TK2MSFTNGP03.phx.gbl...
>>> 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/cp...
>>>
>>> "scott ocamb" <socamb@kiinc.com> wrote in message
>>> news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
>>>>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
>>>>
>>>
>>
>>
>

Tom Moreau

4/1/2007 12:15:00 PM

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/d...
http://sqlblog.com/blogs/tib...

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:O17ToOBdHHA.4468@TK2MSFTNGP03.phx.gbl...
> 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/cp...
>
> "scott ocamb" <socamb@kiinc.com> wrote in message
> news:u8gNMEAdHHA.3648@TK2MSFTNGP05.phx.gbl...
>> 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!!!!!
>>
>>
>> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
>> news:e4AiZf$cHHA.4632@TK2MSFTNGP03.phx.gbl...
>>> 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/cp...
>>>
>>> "scott ocamb" <socamb@kiinc.com> wrote in message
>>> news:%23z0ksQ%23cHHA.2088@TK2MSFTNGP05.phx.gbl...
>>>>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
>>>>
>>>
>>
>>
>

microsoft.public.sqlserver.programming

Service Broker Question

scott ocamb

Tom Moreau

Roger Wolter[MSFT]

scott ocamb

Remus Rusanu [MSFT]

Roger Wolter[MSFT]

Tibor Karaszi

Tom Moreau

x Login to ForumsZone