This topic has not yet been rated - Rate this topic

ALTER SERVICE (Transact-SQL)

SQL Server 2008

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

Arguments

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.

Remarks

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 that use the contract are unaffected.

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

Permissions

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.

Examples

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]) ;

Reference

CREATE SERVICE (Transact-SQL)

DROP SERVICE (Transact-SQL)

EVENTDATA (Transact-SQL)

Other Resources

Creating Service Broker Applications

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD