Export (0) Print
Expand All
Expand Minimize

ALTER SERVICE (Transact-SQL)

Changes an existing service.

Topic link icon Transact-SQL Syntax Conventions


ALTER SERVICE service_name 
   [ ON QUEUE [ schema_name . ]queue_name ] 
   [ ( < opt_arg > [ , ...n ] ) ]
[ ; ]

<opt_arg> ::=
   ADD CONTRACT contract_name | DROP CONTRACT contract_name

service_name

Is the name of the service to change. Server, database, and schema names cannot be specified.

ON QUEUE [ schema_name. ] queue_name

Specifies the new queue for this service. Service Broker moves all messages for this service from the current queue to the new queue.

ADD CONTRACT contract_name

Specifies a contract to add to the contract set exposed by this service.

DROP CONTRACT contract_name

Specifies a contract to delete from the contract set exposed by this service. Service Broker sends an error message on any existing conversations with this service that use this contract.

When the ALTER SERVICE statement deletes a contract from a service, the service can no longer be a target for conversations that use that contract. Therefore, Service Broker does not allow new conversations to the service on that contract. Existing conversations using the contract are unaffected.

To alter the AUTHORIZATION for a service, use the ALTER AUTHORIZATION statement.

Permission for altering a service defaults to the owner of the service, members of the db_ddladmin or db_owner fixed database roles, and members of the sysadmin fixed server role.

A. Changing the queue for a service

The following example changes the //Adventure-Works.com/Expenses service to use the queue NewQueue.

ALTER SERVICE [//Adventure-Works.com/Expenses]
    ON QUEUE NewQueue ;

B. Adding a new contract to the service

The following example changes the //Adventure-Works.com/Expenses service to allow dialogs on the contract //Adventure-Works.com/Expenses.

ALTER SERVICE [//Adventure-Works.com/Expenses]
    (ADD CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission]) ;

C. Adding a new contract to the service, dropping existing contract

The following example changes the //Adventure-Works.com/Expenses service to allow dialogs on the contract //Adventure-Works.com/Expenses/ExpenseProcessing and to disallow dialogs on the contract //Adventure-Works.com/Expenses/ExpenseSubmission.

ALTER SERVICE [//Adventure-Works.com/Expenses]
    (ADD CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing], 
     DROP CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission]) ;

Community Additions

ADD
Show:
© 2014 Microsoft