In SQL Server, the CREATE MESSAGE TYPE statement is used to define a new message type for use with Service Broker. A message type specifies the structure and format of messages that can be sent between services.
Here’s how you can create a message type using the CREATE MESSAGE TYPE statement:
Syntax
CREATE MESSAGE TYPE message_type_name
[ VALIDATION = { NONE | EMPTY | WELL_FORMED_XML | VALID_XML WITH SCHEMA COLLECTION schema_collection_name } ]
- message_type_name: The name of the new message type you are creating. This must be unique within the database.
- VALIDATION: This is optional and specifies the type of validation to be applied to the messages of this type:
- NONE: No validation.
- EMPTY: Only empty messages are allowed.
- WELL_FORMED_XML: Messages must be well-formed XML.
- VALID_XML WITH SCHEMA COLLECTION schema_collection_name: Messages must be valid XML according to a specified XML schema collection.
Example
Here's an example of how to create a new message type called OrderMessage that only allows well-formed XML:
USE YourDatabase; -- Make sure you are using the correct database
CREATE MESSAGE TYPE OrderMessage
VALIDATION = WELL_FORMED_XML;
If you want to allow only empty messages for a different message type, you can do so with the following command:
CREATE MESSAGE TYPE EmptyMessage
VALIDATION = EMPTY;
Additional Considerations
1. Permissions: You must have the appropriate permissions to create message types in the database. Typically, this requires ALTER permission on the database.
2. Naming Conventions: It’s a good practice to use meaningful names for message types in order to easily identify their purpose in your messaging system.
3. Further Operations: After creating a message type, you would typically associate it with a contract using the CREATE CONTRACT statement, which defines the types of messages that can be sent and received by the services involved in communication.
Conclusion
The CREATE MESSAGE TYPE statement is an important step in setting up messaging capabilities in SQL Server Service Broker. Make sure to carefully define the validation requirements based on what you intend to send between services to ensure data integrity and proper message handling.