SQL Server Service Broker – Sending and Receiving


This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

In this installment, we discuss sending and receiving messages.

Sending a message

If you’ve been following along in this series, you’ll have created the necessary components of the service architecture, namely the message types, contract, queues, and services.  You will also have started a conversation between your initiator and target services.  You’re finally ready to start sending and receiving messages in your Service Broker app.  Whoohoo!  Let’s get to it!

To send a message in Service Broker, we use the SEND command.  But first, we need to retrieve our conversation handle.  Remember, all messages need to be sent as part of a conversation.  If this message is unrelated to any previous messages, we might choose to start a new conversation.  However, if this message *is* related to previous messages, we’ll want to reuse an existing conversation so that our messages are processed in the right order.  More on that later.

For the sake of simplicity here, we’ll start a new conversation and send a message on that conversation handle.

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @TaxFormMessage NVARCHAR(1000);

BEGIN TRANSACTION;

--open a dialog between the initiator service and target service

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//SBDemo/Taxes/TaxpayerService]

TO SERVICE N'//SBDemo/Taxes/IRSService'

ON CONTRACT [//SBDemo/Taxes/TaxContract]

WITH ENCRYPTION = OFF;

--build the message

SELECT @TaxFormMessage = 
N'<Form1040EZ>

<SSANumber>695256908</SSANumber>

<LastName>Erickson</LastName>

<FirstName>Gail</FirstName>

<MiddleName>A</MiddleName>

<BirthDate>1952-09-27</BirthDate>

<FilingStatus>M</FilingStatus>

<Wages>66662.00</Wages>

<FederalIncomeTax>12888.00</FederalIncomeTax>

<StateIncomeTax>2522.00</StateIncomeTax>

<SocialSecurityTax>5523.00</SocialSecurityTax>

</Form1040EZ>

';

--send the message using the id of the conversation started above

--specify the Request message, which can only be sent by the conversation initiator

SEND ON CONVERSATION @InitDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/TaxFormMessage]

(@TaxFormMessage);

COMMIT TRANSACTION;

So what happens when we run this?  When we begin a dialog, SQL Server creates  a conversation endpoint in the initiator database.  We can view that endpoint by querying the sys.conversation_endpoints table.  It doesn’t try to communicate with the target service yet, though, so at this point SQL Server doesn’t know whether that target is online or whether it exists at all.  In fact, if we look at sys.conversation_endpoints immediately after beginning the dialog, we’ll see the state_desc of the endpoint as “STARTED_OUTBOUND”.  This means that the conversation has been started, but no messages have been sent yet.

When we issue the SEND command, what happens depends on how our architecture is configured.  So far, our initiator and target services are both in the same database, so when we send a message SQL Server attempts to insert it directly into the target service’s queue.  If for some reason SQL Server can’t write to the target queue (maybe it’s been disabled), then the message gets written to the sys.transmission_queue system table.  SQL Server will continue to try to deliver the message until it succeeds.  Once the message is successfully delivered to the target queue, the message is deleted from the transmission queue.

Receiving messages

Let’s assume our message was successfully delivered.  In fact, let’s verify that it was.  Remember that Service Broker queues are really just hidden tables, and we can select from them.  So if we want to know whether our message reached the target queue, all we need to do is query the queue.

SELECT * FROM IRSQueue

However, note that SELECTing from the queue doesn’t remove messages from that queue.  To pop a message out of the Service Broker queue we must RECEIVE it.

RECEIVE TOP(1) * FROM IRSQueue

Now, if we were to issue the statement above, the message and all of its related information will be returned to our results tab in SSMS.  That’s not very useful, though, is it.  That’s why, typically, we receive messages into variables, so that we can perform additional processing.

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg xml;

DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@RecvReqDlgHandle = conversation_handle,

@RecvReqMsg = cast(message_body as xml),

@RecvReqMsgName = message_type_name

FROM IRSQueue

IF @RecvReqMsgName =

N'//SBDemo/Taxes/TaxFormMessage'

BEGIN

DECLARE

@SSANumber                        varchar(9),

@LastName                        varchar(50),

@FirstName                        varchar(50),

@MiddleName                        varchar(50),

@BirthDate                        date,

@FilingStatus                char(2),

@Wages                                Decimal(12,2),

@FederalIncomeTax        Decimal(12,2),

@StateIncomeTax                Decimal(12,2),

@SocialSecurityTax        Decimal(12,2),

@Refund                                decimal(12,2)

select

@SSANumber                        = c.value(N'(./SSANumber)[1]', N'varchar(9)') ,

@LastName                        = c.value(N'(./LastName)[1]', N'varchar(50)') ,

@FirstName                        = c.value(N'(./FirstName)[1]', N'varchar(50)') ,

@MiddleName                        = c.value(N'(./MiddleName)[1]', N'varchar(50)') ,

@BirthDate                        = c.value(N'(./BirthDate)[1]', N'date') ,

@FilingStatus                = c.value(N'(./FilingStatus)[1]', N'char(2)') ,

@Wages                                = c.value(N'(./Wages)[1]', N'Decimal(12,2)') ,

@FederalIncomeTax        = c.value(N'(./FederalIncomeTax)[1]', N'Decimal(12,2)') ,

@StateIncomeTax                = c.value(N'(./StateIncomeTax)[1]', N'Decimal(12,2)') ,

@SocialSecurityTax        = c.value(N'(./SocialSecurityTax)[1]', N'Decimal(12,2)')

from @RecvReqMsg.nodes('Form1040EZ') T(c)

EXEC CalculateRefund @FilingStatus, @Wages, @FederalIncomeTax, @Refund OUTPUT

DECLARE @ReplyMsg NVARCHAR(100);

IF @Refund &lt; 10000

BEGIN

SELECT @ReplyMsg =

N'<Refund>;'+cast(@Refund as varchar)+'</Refund>;';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/TreasuryCheckMessage]

(@ReplyMsg);

END

ELSE

BEGIN

SELECT @ReplyMsg =

N'<AuditNotice>Refund exceeds $10000: '+cast(@Refund as varchar)+'</AuditNotice>';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE

[//SBDemo/Taxes/AuditNotificationMessage]

(@ReplyMsg);

END

END CONVERSATION @RecvReqDlgHandle;

END

COMMIT TRANSACTION;

GO

There are a few points I’d like to highlight in the code above.  First, when we receive the message, we don’t just receive the body of the message, we’re also receiving the conversation_handle and the message_type_name.  This is typical of Service Broker apps.  Grabbing the conversation_handle allows us to send a reply message on the same conversation, which we actually do later in the code.  Getting the message type allows us to add logic to process different message types in different ways, even though they’re coming in on the same queue.

Secondly, note that we need to cast the message_body as an xml variable.  The message_body is stored as a varbinary(max), so we need to convert it to xml before we can shred it.

Finally, the RECEIVE statement is inside BEGIN and COMMIT transaction statements, so we’re receiving the message as part of the transaction.  Therefore, if we hit an error, maybe in the CalculateRefund procedure, the entire transaction will roll back and the message will go back into the queue.  And the next time we run the code, this message will be received and processed again.  Depending on what caused the error, we may run into the same problem.  Can you see where this might be an issue?  More on that soon.

Conclusion

I hope you’re enjoying this series of posts as much as I am.  We’ve only scratched the surface, so much more to come!  If there’s a specific area of Service Broker you want to see addressed, please feel free to leave a suggestion in the comments.

Also recommended:


Leave a Reply to Nishad Jadhav Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

5 thoughts on “SQL Server Service Broker – Sending and Receiving