You are a database administrator for NuTex Corporation. Your company maintains survey data in the Research database residing on an instance of SQL Server 2008. You are creating a Service Broker application to send and receive messages between two services, //NuTex.com/SurveyInput and //NuTex.com/ Respondent.
You want to receive survey-related information from another organization that maintains data on a different database, Prod. The survey data sent from the Prod database is in XML format. At periodic intervals, the Research database will initiate a conversation to request survey response data.
You have created the message type to validate the incoming survey data using the following statement:
CREATE MESSAGE TYPE [//NuTex.com/Research/SurveyData] VALIDATION = WELL_FORMED_XML;
Which statement should you use to create the contract to be used in conversations?
A. CREATE CONTRACT [//NuTex.com/Research/SurveyData/Contract1]
([//NuTex.com/Research/SurveyData] SENT BY TARGET);
B. CREATE CONTRACT [//NuTex.com/Research/SurveyData/Contract1]
([//NuTex.com/Research/SurveyData] SENT BY ANY);
C. CREATE CONTRACT [//NuTex.com/Research/SurveyData/Contract1]
(SurveyData SENT BY INITIATOR);
D. CREATE CONTRACT [//NuTex.com/Research/SurveyData/Contract1]
([DEFAULT] SENT BY ANY);
Correct Answer: B
Explanation/Reference:
You should use the following statement to create the contract:
CREATE CONTRACT [//NuTex.com/Research/SurveyData/Contract1]([//NuTex.com/Research/SurveyData] SENT BY ANY);
In a Service Broker conversation, a contract defines the types of messages allowed and the conversation participants that can send these messages. In this scenario, you created a message type named //NuTex.com/Research/SurveyData. You can then use the CREATE CONTRACT statement to create the contract to be used in conversations. The syntax of the CREATE CONTRACT statement is as follows:
CREATE CONTRACT contract_name [AUTHORIZATION owner_name]({{message_type | [DEFAULT]}SENT BY {INITIATOR | TARGET | ANY}
[,…n]);
On the one hand, the Research database is the initiator and receives messages from the Prod database. On the other hand, a contract must allow the initiator to send a message. So the only acceptable option here is B. The CREATE CONTRACT statement fails when the contract does not contain at least one message type that is SENT BY ANY or SENT BY INITIATOR.
You should not use the statement that includes the SENT BY TARGET clause because this statement will generate an error. Specifying the SENT BY TARGET clause indicates that only the target in the conversation can send messages of the specified message type. Each contract must contain at least one message specifying SENT BY INITIATOR or SENT BY ANY, or an error occurs.
You should not use the statement that specifies SurveyData as the message type because this statement will generate a syntax error. In this scenario, the name of the message type is //NuTex.com/Research/SurveyData.
You should not use the statement that specifies [DEFAULT] as the message type because the [DEFAULT] message type has validation set to NONE, indicating the data is not validated. In this scenario, the data is in XML format and requires the validation as specified in the message type you created. When you create a message type, you can specify one of the following types of validation:
EMPTY: Indicates that the message body will be NULL.
NONE: Indicates that validation will not be performed. This type of validation is used for binary data.
WELL_FORMED_XML: Indicates that the message body will contain well-formed XML data.
VALID_XML WITH SCHEMA COLLECTION schema_collection_name: Indicates that the message body will contain XML data that will be validated against a predefined XML schema in the specified schema collection.