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:
- SQL Server Service Broker – Introduction
- SQL Server Service Broker – Service Architecture
- SQL Server Service Broker – Conversation Architecture
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 < 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;
GOThere 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.