Export (0) Print
Expand All
Expand Minimize

CREATE CONTRACT (Transact-SQL)

Creates a new contract. A contract defines the message types used in a Service Broker conversation and also determines which side of the conversation can send messages of that type. Each conversation follows a contract. The initiating service specifies the contract for the conversation when the conversation begins. The target service specifies the contracts that the target service accepts conversations for.

Topic link icon Transact-SQL Syntax Conventions


CREATE CONTRACT contract_name
   [ AUTHORIZATION owner_name ]
      (  {   { message_type_name | [ DEFAULT ] }
          SENT BY { INITIATOR | TARGET | ANY } 
       } [ ,...n] ) 
[ ; ]

contract_name

Is the name of the contract to create. A new contract is created in the current database and owned by the principal specified in the AUTHORIZATION clause. Server, database, and schema names cannot be specified. The contract_name may be up to 128 characters.

AUTHORIZATION owner_name

Sets the owner of the contract to the specified database user or role. When the current user is dbo or sa, owner_name may be the name of any valid user or role. Otherwise, owner_name must be the name of the current user, the name of a user that the current user has impersonate permissions for, or the name of a role to which the current user belongs. When this clause is omitted, the contract belongs to the current user.

message_type_name

Is the name of a message type to be included as part of the contract.

SENT BY

Specifies which endpoint can send a message of the indicated message type. Contracts document the messages that services can use to have specific conversations. Each conversation has two endpoints: the initiator endpoint, the service that started the conversation, and the target endpoint, the service that the initiator is contacting.

INITIATOR

Indicates that only the initiator of the conversation can send messages of the specified message type. A service that begins a conversation is referred to as the initiator of the conversation.

TARGET

Indicates that only the target of the conversation can send messages of the specified message type. A service that accepts a conversation that was started by another service is referred to as the target of the conversation.

ANY

Indicates that messages of this type can be sent by both the initiator and the target.

[ DEFAULT ]

Indicates that this contract supports messages of the default message type. By default, all databases contain a message type named DEFAULT. This message type uses a validation of NONE. In the context of this clause, DEFAULT is not a keyword, and must be delimited as an identifier. Microsoft SQL Server also provides a DEFAULT contract which specifies the DEFAULT message type.

The order of message types in the contract is not significant. After the target has received the first message, Service Broker allows either side of the conversation to send any message allowed for that side of the conversation at any time. For example, if the initiator of the conversation can send the message type //Adventure-Works.com/Expenses/SubmitExpense, Service Broker allows the initiator to send any number of SubmitExpense messages at any time during the conversation.

The message types and directions in a contract cannot be changed. To change the AUTHORIZATION for a contract, use the ALTER AUTHORIZATION statement.

A contract must allow the initiator to send a message. 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.

Regardless of the contract, a service can always receive the message types http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer, http://schemas.microsoft.com/SQL/ServiceBroker/Error, and http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog. Service Broker uses these message types for system messages to the application. For more information about broker-provided message types, see Broker Messages.

A contract may not be a temporary object. Contract names beginning with # are permitted, but are permanent objects.

By default, members of the db_ddladmin or db_owner fixed database roles and the sysadmin fixed server role may create contracts.

By default, the owner of the contract, members of the db_ddladmin or db_owner fixed database roles, and members of the sysadmin fixed server role have REFERENCES permission on a contract.

The user executing the CREATE CONTRACT statement must have REFERENCES permission on all message types specified.

A. Creating a contract

The following example creates an expense reimbursement contract based on three message types.

CREATE MESSAGE TYPE
    [//Adventure-Works.com/Expenses/SubmitExpense]         
    VALIDATION = WELL_FORMED_XML ;         

CREATE MESSAGE TYPE
    [//Adventure-Works.com/Expenses/ExpenseApprovedOrDenied]         
    VALIDATION = WELL_FORMED_XML ;         

CREATE MESSAGE TYPE         
    [//Adventure-Works.com/Expenses/ExpenseReimbursed]         
    VALIDATION= WELL_FORMED_XML ;         

CREATE CONTRACT          
    [//Adventure-Works.com/Expenses/ExpenseSubmission]         
    ( [//Adventure-Works.com/Expenses/SubmitExpense]         
          SENT BY INITIATOR,         
      [//Adventure-Works.com/Expenses/ExpenseApprovedOrDenied]         
          SENT BY TARGET,         
      [//Adventure-Works.com/Expenses/ExpenseReimbursed]         
          SENT BY TARGET         
    ) ;

Community Additions

ADD
Show:
© 2014 Microsoft