You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 hosts two database named CK_Staff and CK_Projects.
You need to create a Service Broker to enable the CK_Staff and CK_Projects databases to communicate. You create three message types using the following Transact-SQL statements:
CREATE MESSAGE TYPE [// Domain.com/Staff/Name] VALIDATION = WELL_FORMED_XML ;
CREATE MESSAGE TYPE [// Domain.com/Projects/Budget] VALIDATION = WELL_FORMED_XML ;
CREATE MESSAGE TYPE [// Domain.com/Projects/Completion] VALIDATION = EMPTY ;
You now want to create a contract named // Domain.com/Project/Progress between two services that include the two message types. You want to ensure that both the sender and the target is able to use // Domain.com/Staff/Name message type; but that only the sender is able to use the // Domain.com/Projects/Budget message type, and only the target is able to use the // Domain.com/Projects/Completion message type.
Which CREATE CONTRACT statement should you use?
A.
CREATE CONTRACT [// Domain.com/Project/Progress] ([// Domain.com/Staff/Name] SENT BY ANY
[// Domain.com/Projects/Budget] SENT BY ANY
[// Domain.com/ Projects/Completion] SENT BY ANY)
B.
CREATE CONTRACT [// Domain.com/Project/Progress] ([// Domain.com/Staff/Name] SENT BY ANY
[// Domain.com/Projects/Budget] SENT BY TARGET [// Domain.com/ Projects/Completion] SENT BY INITIATOR)
C.
CREATE CONTRACT [// Domain.com/Project/Progress] ([// Domain.com/Staff/Name] SENT BY ANY
[// Domain.com/Projects/Budget] SENT BY INITIATOR [// Domain.com/ Projects/Completion] SENT BY TARGET)
D.
CREATE CONTRACT [// Domain.com/Project/Progress] ([// Domain.com/Staff/Name] SENT BY TARGET
[// Domain.com/Staff/Name] SENT BY INITIATOR
[// Domain.com/Projects/Budget] SENT BY INITIATOR [// Domain.com/ Projects/Completion] SENT BY TARGET)
E.
CREATE CONTRACT [// Domain.com/Project/Progress] ([// Domain.com/Staff/Name] SENT BY TARGET
[// Domain.com/Staff/Name] SENT BY INITIATOR
[// Domain.com/Projects/Budget] SENT BY TARGET [// Domain.com/ Projects/Completion] SENT BY INITIATOR)
Explanation:
The // Domain.com/Staff/Name message must be used by both sender and target; therefore your CREATE CONTRACT statement must include the [// Domain.com/Staff/Name] SENT BY ANY line. Only the sender must be able to use the // Domain.com/Projects/Budget message type; therefore your CREATE CONTRACT statement must include the [// Domain.com/Projects/Budget] SENT BY INITIATOR. Only the target must be able to use the
// Domain.com/Projects/Completion message type; therefore your CREATE CONTRACT statement must include the [// Domain.com/Projects/Completion] SENT BY TARGET.
Incorrect Answers:
A: The [// Domain.com/Projects/Budget] SENT BY ANY, [// Domain.com/Projects/Completion] SENT BY ANY lines allows both sender and target to use the // Domain.com/Projects/Budget and // Domain.com/Projects/Completion message types. However, only the sender must be able to use the // Domain.com/Projects/Budget message type, and only the target must be able to use the // Domain.com/Projects/Completion message type. B, E: The [// Domain.com/Projects/Budget] SENT BY TARGET, [// Domain.com/Projects/Completion] SENT BY INITIATOR lines allow the target to use the // Domain.com/Projects/Budget and the sender to use the // Domain.com/Projects/Completion message types. However, the sender must be able to use the // Domain.com/Projects/Budget message type, and the target must be able to use the // Domain.com/Projects/Completion message type.
D: The [// Domain.com/Staff/Name] SENT BY TARGET, [// Domain.com/Staff/Name] SENT BY INITIATOR, line allows both sender and target to use the // Domain.com/Projects/Name message types. However, the correct syntax to use here is [// Domain.com/Projects/Budget] SENT BY ANY.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: CREATE CONTRACT statement