Archive for category: Articles

Service Broker is not a black box.  SQL Server Service Broker Sending and Receiving is part of a blog series designed to support your messaging needs and make sense of Service Broker.

SQL Server Service Broker Sending and Receiving

Originally published on ColleenMorrow.com.

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;

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. Next I will continue with SQL Server Service Broker Error Handling.

About the Author

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.

<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>

</ol>

About UpSearch

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.