Sequentially processing messages in Service Broker Queue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » Service Broker » Sequentially processing messages in Service...

11 posts, Page 1 of 2

1 2 »»

Sequentially processing messages in Service Broker Queue

Rate Topic

Display Mode

Topic Options

Author

Message

prasanthev

Posted Thursday, May 19, 2011 7:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
Points: 3, Visits: 60

I am using service broker to execute the SSIS package. Queue is created with Activation ON and created an Activation Stored proc which will invoke the SSIS package. Now if i send more than one message using service broker multiple instance of the SSIS package is getting executed simultaneously. Is there a way to configue the queue is such a way that if one message is under process it will wait till that process get completed and then only it will process the next message.

Thanks,
Prasanth

Post #1111654

Nils Gustav Stråbø

Posted Friday, May 20, 2011 11:54 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:52 AM
Points: 1,788, Visits: 3,327

That depends how you execute that SSIS package from the activation procedure.

If you can show the code, the I'll get a better understanding on how you receive a message from the queue and how that message is used to execute the package.

Post #1112618

Evil Kraig F

Posted Friday, May 20, 2011 12:11 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

Let the queue have only one queue reader. You set it as an option during the create queue command.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1112634

Nils Gustav Stråbø

Posted Friday, May 20, 2011 12:19 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:52 AM
Points: 1,788, Visits: 3,327

But it still depends how the SSIS package is executed. If the T-SQL statement that starts the package "finishes" before package execution is completed, the activation procedure will get the next message and start another instance of the SSIS package before the first one has completed.

Post #1112637

Evil Kraig F

Posted Friday, May 20, 2011 12:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

Nils Gustav Stråbø (5/20/2011)

Yeeps! Good point, nice catch. Smile

Post #1112639

LutzM

Posted Saturday, May 21, 2011 3:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

You could check if your Activation Stored proc is running before processing the next message.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1112867

prasanthev

Posted Monday, May 23, 2011 12:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
Points: 3, Visits: 60

Here is the script of my Activation Proc:

CREATE PROCEDURE [dbo].[usp_ExecutePackage]
WITH EXECUTE AS 'dbo'
AS
BEGIN

SET ARITHABORT ON
set nocount on
set xact_abort on

DECLARE @PackageName VARCHAR(500)
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRY
BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM InstTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
(@ReplyMsg);

END CONVERSATION @RecvReqDlgHandle;
END

--SELECT @ReplyMsg AS SentReplyMsg;

IF @RecvReqMsg='<RequestMsg>TestPackage</RequestMsg>'
EXEC dbo.usp_ExecutePackage_TestPackage
ELSE IF @RecvReqMsg='<RequestMsg>TestPackage1</RequestMsg>'
EXEC usp_ExecutePackage_TestPackage1

-- Display recieved request.
--SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

INSERT INTO [InstTargetDB].[dbo].[Error_log]
([ERROR_NUMBER]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]
,[ErrorLine]
,[ErrorMessage])
SELECT
ERROR_NUMBER() AS ErrorNumber,
CAST(ERROR_SEVERITY() AS VARCHAR(100)) AS ErrorSeverity,
CAST(ERROR_STATE()AS VARCHAR(100)) as ErrorState,
CAST(ERROR_PROCEDURE()AS VARCHAR(100)) as ErrorProcedure,
CAST(ERROR_LINE()AS VARCHAR(100)) as ErrorLine,
CAST(ERROR_MESSAGE()AS VARCHAR(2000)) as ErrorMessage;

END CATCH

END

Post #1113133

prasanthev

Posted Monday, May 23, 2011 12:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
Points: 3, Visits: 60

ALTER QUEUE InstTargetQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = usp_ExecutePackage,
MAX_QUEUE_READERS = 20,
EXECUTE AS 'dbo'
);

Do you mean to say i have to set MAX_QUEUE_READERS = 1 ? So that it will not invoke the Activation Proc if one instance is already executing???

Post #1113134

Nils Gustav Stråbø

Posted Monday, May 23, 2011 10:06 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:52 AM
Points: 1,788, Visits: 3,327

How does usp_ExecutePackage_TestPackage and usp_ExecutePackage_TestPackage1 execute the SSIS package? Is control returned to the procedure before the SSIS package has completed? If so, then setting max queue readers to 1 won't help. If the SSIS package has to complete before control is returned to the procedure, then setting max queue readers to 1 will help.

Post #1113437

mupparaju78

Posted Wednesday, September 07, 2011 1:27 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:04 AM
Points: 20, Visits: 558

Hi,
how can i call ssis package from service broker queue.

Post #1170861

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions