CREATE SERVICE (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
Permission for creating a service defaults to members of the db_ddladmin or db_owner fixed database roles and 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, and 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, and 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.
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 ;