none
Using SQL Broker Service for One way messaging

    Question

  • Hi All

    I am novice to SQL Server Broker Service. I am trying to use the Broker service In one way messaging fashion. That means client application send message to queue and using internal activation on queue, I get the message and process it.

    Just wanted to check does anybody sees any issue with this approach.

    Step which I have implemented:

    1) Create a message type and contract

    2) Create a queue and attach a procedure to queue which can recive message from queue and process this.

    3) Define a service on queue.

    4) Define a store procedure which send a message to queue and called by client,

    In this I am using "From Service" And "To Service" as same.

    Thanks

    Deepak Sanghi


    Deepak Sanghi Happy Biztalking.........

    Saturday, June 15, 2013 8:01 PM

All replies

  • I am novice to SQL Server Broker Service. I am trying to use the Broker service In one way messaging fashion. That means client application send message to queue and using internal activation on queue, I get the message and process it.

    Service Broker conversations are dialogs, meaning 2-way conversations.  It is possible to use the same queue for both the initiator and target services.  In this case, the activated proc processes messages sent to the target as well as messages sent back to the initiator (e.g. EndDialog and Error messages).

    Below is an example script which logs errors sent back to the initiator service.

    CREATE TABLE dbo.ServiceBrokerErrorLog(
    	ErrorTimestamp datetime2(7)
    	,ErrorMessageText nvarchar(3000)
    	,MessageBody varbinary(MAX)
    	);
    GO
    
    CREATE PROC dbo.usp_SharedQueueActivatedProc
    AS
    
    DECLARE
    	@conversation_handle uniqueidentifier
    	, @message_type_name nvarchar(256)
    	, @message_body varbinary(MAX)
    	, @MessageText nvarchar(1000)
    	, @error_message nvarchar(3000);
    WHILE 1 = 1
    BEGIN
    
    	WAITFOR (
    		RECEIVE TOP(1) 
    			@conversation_handle = conversation_handle
    			,@message_type_name = message_type_name
    			,@message_body = message_body
    		FROM dbo.SharedQueue
    		), TIMEOUT 1000;
    
    	IF @@ROWCOUNT = 0 
    	BEGIN
    		BREAK;
    	END
    
    	IF @message_type_name = N'MyMessageType'
    	BEGIN
    		SET @MessageText = CAST(@message_body AS nvarchar(1000));
    		BEGIN TRY
    			-- *** do something with message here ***
    	
    			END CONVERSATION @conversation_handle;
    		END TRY
    		BEGIN CATCH
    			SET @error_message = ERROR_MESSAGE();
    			END CONVERSATION @conversation_handle WITH ERROR = 1 DESCRIPTION = @error_message;
    		END CATCH;
    
    	END
    	ELSE
    	IF @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    	BEGIN
    		END CONVERSATION @conversation_handle;
    	END
    	ELSE
    	BEGIN
    		--anything other than user type or EndDialog is an unexpected error
    		SET @error_message = N'Unexpected message type received: ' + @message_type_name; 
    		INSERT INTO dbo.ServiceBrokerErrorLog VALUES(
    			SYSDATETIME()
    			, @error_message
    			, @message_body
    			);
    		END CONVERSATION @conversation_handle WITH ERROR = 1 DESCRIPTION = @error_message;
    	END
    END;
    GO
    
    CREATE QUEUE SharedQueue
          WITH STATUS=ON,
          ACTIVATION (
              PROCEDURE_NAME = usp_SharedQueueActivatedProc,
              MAX_QUEUE_READERS = 1,
              EXECUTE AS OWNER
    		);
    GO
    
    CREATE SERVICE MyInitiatorService
    	ON QUEUE dbo.SharedQueue
    	( MyContract );
    GO
    
    CREATE SERVICE MyTargetService
       ON QUEUE dbo.SharedQueue
    	( MyContract );
    GO
    
    CREATE PROC dbo.usp_SendMessage
    	@MessageText nvarchar(1000)
    AS
    DECLARE @dialog_handle uniqueidentifier;
    
    BEGIN DIALOG CONVERSATION @dialog_handle
       FROM SERVICE MyInitiatorService
       TO SERVICE 'MyTargetService'
       ON CONTRACT MyContract
       WITH ENCRYPTION = OFF;
    
    SEND ON CONVERSATION @dialog_handle
    	MESSAGE TYPE MyMessageType (@MessageText);
    GO
    
    EXEC dbo.usp_SendMessage N'This is a test message';
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 15, 2013 9:31 PM
  • Hi Dan

    Thanks for your reply. I have been using same code which you pasted here. The only difference is, I am creating a single service and while sending the message using the same service. Do you see any issue with this?

    BEGIN DIALOG CONVERSATION @dialog_handle
      
    FROMSERVICE MyInitiatorService
      
    TOSERVICE 'MyInitiatorService'
      
    ON CONTRACT MyContract
      
    WITH ENCRYPTION = OFF;

    SEND
    ON CONVERSATION @dialog_handle
    MESSAGE TYPE MyMessageType
    (@MessageText);
    GO

    Thanks

    Deepak Sanghi


    Deepak Sanghi Happy Biztalking.........

    Sunday, June 16, 2013 4:12 AM
  • Thanks for your reply. I have been using same code which you pasted here. The only difference is, I am creating a single service and while sending the message using the same service. Do you see any issue with this?

    Yes, you can have the same service play the roles of both initiator and target.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 16, 2013 4:22 AM