Partilhar via

ALTER SERVICE (Transact-SQL)

  • Artigo

Changes an existing service.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

See Also

Reference

CREATE SERVICE (Transact-SQL)
DROP SERVICE (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

Creating Service Broker Applications

Help and Information

Getting SQL Server 2005 Assistance