SQL Server 2008 : Service Broker - Message Types

1/22/2011 4:06:16 PM
Message Types are the first of the types of objects that we will be looking at. The Message Type is simply a definition of the type of data that will be contained within the message. When you create a Message Type you define the type of validation that the SQL SERVICE BROKER will be performing on the body of the message. You have four options for this validation. They are NONE, EMPTY, WELL_FORMED_XML, and VALID_XML WITH SCHEMA COLLECTION, with NONE being the default. As with all other database objects Message Types are created under the ownership of a specific database user or database role. Although it is most common to create the objects under the ownership of dbo, any valid database user or role can be specified.


Make sure that you know the rules for each of the validation options of the MESSAGE TYPES.

Message Types are created using the CREATE MESSAGE TYPE command. Like all the other objects within the SQL SERVICE BROKER Message Type, objects are database specific. The name of the Message Type can be up to 128 characters in length. Although you can use any object name for any SQL SERVICE BROKER objects, since the messages can be sent between systems great care should be used when naming objects so as to ensure that there are no naming collisions when sending messages between servers. One technique is to use a UNC style naming convention, which helps keep SERVICE BROKER object names from colliding with each other when you start sending messages between servers.

The validation type NONE is used when you wish to put any type of data within the message body. This includes text, numbers, XML, or binary data. When using this validation type, you can send any data you like within the message body, except for a NULL value. If you want to ensure that an empty message is sent, you have to specify the validation type EMPTY.

The validation type WELL_FORMED_XML requires that the data you are inserting into the message is correctly formed XML. This saves you from having to write your own validation logic at the receiving side of the message to ensure that the data is a valid XML document.

The validation type VALID_XML WITH SCHEMA COLLECTION requires not only that your XML data be valid, but that it meets the requirements of the predefined XML schema collection. The XML schema collection should be an already defined XML schema definition created within the SQL Server database. Although the creation of XML schemas is outside the scope of this article, you can read more about it by referencing Books OnLine under the index heading “CREATE XML SCHEMA COLLECTION statement.” When using this validation type, before sending the message SQL SERVICE BROKER will first check that the XML document you are sending fits the required XML schema definition; if it does the message will be sent. If the message does not fit the XML schema definition the message will not be sent, and an error message will be returned to the calling code.

The actual syntax for creating a Message Type is very simple and straightforward.

CREATE MESSAGE TYPE [YourApplication/YourMessageType]

As you can see in the sample code, the name of the Message Type is YourApplication/YourMessageType. The Message Type will be owned by dbo, and there will be no validation performed. I most often use the validation of NONE or EMPTY because I prefer to have my own logic on the receiving side handle checking if the XML is correct. I also do not want the SQL Server engine to have to spend extra time checking that the XML fits the official definition of “well-formed XML.”

If you wish to change the validation after you create the Message Type you can alter the Message Type by using the ALTER MESSAGE TYPE command. The authorization cannot be changed after the Message Type has been created.

Exercise . Creating Message Types

Create four message types within the AdventureWorks database. Use the validation NONE for two of them and WELL_FORMED_XML for the other two. Create the Message Types based on the data shown in Table 1.

Table 1. Message Types and Their Validations
Message Type NameMessage Type Validation
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone