CREATE SERVICE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Creates a new service. A Service Broker service is a name for a specific task or set of tasks. Service Broker uses the name of the service to route messages, deliver messages to the correct queue within a database, and enforce the contract for a conversation.
Transact-SQL syntax conventions
Syntax
CREATE SERVICE service_name
[ AUTHORIZATION owner_name ]
ON QUEUE [ schema_name. ]queue_name
[ ( contract_name | [DEFAULT][ ,...n ] ) ]
[ ; ]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
service_name
Is the name of the service to create. A new service 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 service_name must be a valid sysname.
Note
Do not create a service that uses the keyword ANY for the service_name. When you specify ANY
for a service name in CREATE BROKER PRIORITY
, the priority is considered for all services. It is not limited to a service whose name is ANY.
AUTHORIZATION owner_name
Sets the owner of the service 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 permission for, or the name of a role to which the current user belongs.
ON QUEUE [ schema_name. ] queue_name
Specifies the queue that receives messages for the service. The queue must exist in the same database as the service. If no schema_name is provided, the schema is the default schema for the user that executes the statement.
contract_name
Specifies a contract for which this service may be a target. Service programs initiate conversations to this service using the contracts specified. If no contracts are specified, the service may only initiate conversations.
[DEFAULT]
Specifies that the service may be a target for conversations that follow the DEFAULT contract. In the context of this clause, DEFAULT is not a keyword, and must be delimited as an identifier. The DEFAULT contract allows both sides of the conversation to send messages of message type DEFAULT. Message type DEFAULT uses validation NONE.
Remarks
A service exposes the functionality provided by the contracts with which it is associated, so that they can be used by other services. The CREATE SERVICE
statement specifies the contracts that this service is the target for. A service can only be a target for conversations that use the contracts specified by the service. A service that specifies no contracts exposes no functionality to other services.
Conversations initiated from this service may use any contract. You create a service without specifying contracts when the service only initiates conversations.
When Service Broker accepts a new conversation from a remote service, the name of the target service determines the queue where the broker places messages in the conversation.
Permissions
Permission for creating a service defaults to members of the db_ddladmin
or db_owner
fixed database roles, or the sysadmin
fixed server role. The user executing the CREATE SERVICE
statement must have REFERENCES
permission on the queue and all contracts specified.
REFERENCES
permission for a service defaults to the owner of the service, members of the db_ddladmin
or db_owner
fixed database roles, or members of the sysadmin
fixed server role. SEND
permissions for a service default to the owner of the service, members of the db_owner
fixed database role, or members of the sysadmin
fixed server role.
A service may not be a temporary object. Service names beginning with # are allowed, but are permanent objects.
Examples
A. Creating a service with one contract
The following example creates the service //Adventure-Works.com/Expenses
on the ExpenseQueue
queue in the dbo
schema. Dialogs that target this service must follow the contract //Adventure-Works.com/Expenses/ExpenseSubmission
.
CREATE SERVICE [//Adventure-Works.com/Expenses]
ON QUEUE [dbo].[ExpenseQueue]
([//Adventure-Works.com/Expenses/ExpenseSubmission]) ;
B. Creating a service with multiple contracts
The following example creates the service //Adventure-Works.com/Expenses
on the ExpenseQueue
queue. Dialogs that target this service must either follow the contract //Adventure-Works.com/Expenses/ExpenseSubmission
or the contract //Adventure-Works.com/Expenses/ExpenseProcessing
.
CREATE SERVICE [//Adventure-Works.com/Expenses] ON QUEUE ExpenseQueue
([//Adventure-Works.com/Expenses/ExpenseSubmission],
[//Adventure-Works.com/Expenses/ExpenseProcessing]) ;
C. Creating a service with no contracts
The following example creates the service //Adventure-Works.com/Expenses on the ExpenseQueue
queue. This service has no contract information. Therefore, the service can only be the initiator of a dialog.
CREATE SERVICE [//Adventure-Works.com/Expenses] ON QUEUE ExpenseQueue ;
See Also
ALTER SERVICE (Transact-SQL)
DROP SERVICE (Transact-SQL)
EVENTDATA (Transact-SQL)