Service Broker Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.conversation_endpoints
• sys.conversation_groups
• sys.conversation_priorities
• sys.message_type_xml_schema_collection_usages
• sys.remote_service_bindings
• sys.routes
• sys.server_role_members
• sys.service_contract_message_usages
• sys.service_contract_usages
• sys.service_contracts
• sys.service_message_types
• sys.service_queue_usages
• sys.service_queues
• sys.services
• sys.transmission_queue

sys.conversation_endpoints

Each side of a Service Broker conversation is represented by a conversation endpoint. This catalog view contains a row per conversation endpoint in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
conversation_handle	•	•	•	•	uniqueidentifier	Identifier for this conversation endpoint. Not NULLABLE.
conversation_id	•	•	•	•	uniqueidentifier	Identifier for the conversation. This identifier is shared by both participants in the conversation. This together with the is_initiator column is unique within the database. Not NULLABLE.
is_initiator	•	•	•	•	tinyint	Whether this endpoint is the initiator or the target of the conversation. Not NULLABLE. 1 = Initiator 0 = Target
service_contract_id	•	•	•	•	int	Identifier of the contract for this conversation. Not NULLABLE.
conversation_group_id	•	•	•	•	uniqueidentifier	Identifier for the conversation group this conversation belongs to. Not NULLABLE.
service_id	•	•	•	•	int	Identifier for the service for this side of the conversation. Not NULLABLE.
lifetime	•	•	•	•	datetime	Expiration date/time for this conversation. Not NULLABLE.
state	•	•	•	•	char(2)	The current state of the conversation. Not NULLABLE. One of: SO Started outbound. SQL Server processed a BEGIN CONVERSATION for this conversation, but no messages have yet been sent. SI Started inbound. Another instance started a new conversation with SQL Server, but SQL Server has not yet completely received the first message. SQL Server may create the conversation in this state if the first message is fragmented or SQL Server receives messages out of order. However, SQL Server may create the conversation in the CO state if the first transmission received for the conversation contains the entire first message. CO Conversing. The conversation is established, and both sides of the conversation may send messages. Most of the communication for a typical service takes place when the conversation is in this state. DI Disconnected inbound. The remote side of the conversation has issued an END CONVERSATION. The conversation remains in this state until the local side of the conversation issues an END CONVERSATION. An application may still receive messages for the conversation. Since the remote side of the conversation has ended the conversation, an application may not send messages on this conversation. When an application issues an END CONVERSATION, the conversation moves to the CD state. DO Disconnected outbound. The local side of the conversation has issued an END CONVERSATION. The conversation remains in this state until the remote side of the conversation acknowledges the END CONVERSATION. An application may not send or receive messages for the conversation. When the remote side of the conversation acknowledges the END CONVERSATION, the conversation moves to the CD state. ER Error. An error has occurred on this endpoint. The Error, Severity, and State columns contain information on the specific error that occurred. CD Closed. The conversation endpoint is no longer in use.
state_desc	•	•	•	•	nvarchar(60)	Description of endpoint conversation state, one of: STARTED_OUTBOUND STARTED_INBOUND CONVERSING DISCONNECTED_INBOUND DISCONNECTED_OUTBOUND CLOSED ERROR This column is NULLABLE.
far_service	•	•	•	•	nvarchar(256)	Name of the service on the remote side of conversation. Not NULLABLE.
far_broker_instance	•	•	•	•	nvarchar(128)	The broker instance for the remote side of the conversation. NULLABLE.
principal_id	•	•	•	•	int	Identifier of the principal whose certificate is used by the local side of the dialog. Not NULLABLE.
far_principal_id	•	•	•	•	int	Identifier of the user whose certificate is used by the remote side of the dialog. Not NULLABLE.
outbound_session_key_identifier	•	•	•	•	uniqueidentifier	Identifier for outbound encryption key for this dialog. Not NULLABLE.
inbound_session_key_identifier	•	•	•	•	uniqueidentifier	Identifier for inbound encryption key for this dialog. Not NULLABLE.
security_timestamp	•	•	•	•	datetime	Time at the local session key was created. Not NULLABLE.
dialog_timer	•	•	•	•	datetime	The time at which the conversation timer for this dialog sends a DialogTimer message. Not NULLABLE.
send_sequence	•	•	•	•	bigint	Next message number in the send sequence. Not NULLABLE.
last_send_tran_id	•	•	•	•	binary(6)	Internal transaction ID of last transaction to send a message. Not NULLABLE.
end_dialog_sequence	•	•	•	•	bigint	The sequence number of the End Dialog message. Not NULLABLE.
receive_sequence	•	•	•	•	bigint	Next message number expected in message receive sequence. Not NULLABLE.
receive_sequence_frag	•	•	•	•	int	Next message fragment number expected in message receive sequence. Not NULLABLE.
system_sequence	•	•	•	•	bigint	The sequence number of the last system message for this dialog. Not NULLABLE.
first_out_of_order_sequence	•	•	•	•	bigint	The sequence number of the first message in the out of order messages for this dialog. Not NULLABLE.
last_out_of_order_sequence	•	•	•	•	bigint	The sequence number of the last message in the out of order messages for this dialog. Not NULLABLE.
last_out_of_order_frag	•	•	•	•	int	Sequence number of the last message in the out of order fragments for this dialog. Not NULLABLE.
is_system	•	•	•	•	bit	1 if this is a system dialog. Not NULLABLE.
priority		•	•	•	tinyint	The conversation priority that is assigned to this conversation endpoint. Not NULLABLE.

TSQL

Sql 2005
SELECT [conversation_handle], [conversation_id], [is_initiator], [service_contract_id], [conversation_group_id], [service_id], [lifetime], [state], [state_desc], [far_service], [far_broker_instance], [principal_id], [far_principal_id], [outbound_session_key_identifier], [inbound_session_key_identifier], [security_timestamp], [dialog_timer], [send_sequence], [last_send_tran_id], [end_dialog_sequence], [receive_sequence], [receive_sequence_frag], [system_sequence], [first_out_of_order_sequence], [last_out_of_order_sequence], [last_out_of_order_frag], [is_system] FROM sys.conversation_endpoints
Sql 2008
SELECT [conversation_handle], [conversation_id], [is_initiator], [service_contract_id], [conversation_group_id], [service_id], [lifetime], [state], [state_desc], [far_service], [far_broker_instance], [principal_id], [far_principal_id], [outbound_session_key_identifier], [inbound_session_key_identifier], [security_timestamp], [dialog_timer], [send_sequence], [last_send_tran_id], [end_dialog_sequence], [receive_sequence], [receive_sequence_frag], [system_sequence], [first_out_of_order_sequence], [last_out_of_order_sequence], [last_out_of_order_frag], [is_system], [priority] FROM sys.conversation_endpoints
Sql 2008 R2
SELECT [conversation_handle], [conversation_id], [is_initiator], [service_contract_id], [conversation_group_id], [service_id], [lifetime], [state], [state_desc], [far_service], [far_broker_instance], [principal_id], [far_principal_id], [outbound_session_key_identifier], [inbound_session_key_identifier], [security_timestamp], [dialog_timer], [send_sequence], [last_send_tran_id], [end_dialog_sequence], [receive_sequence], [receive_sequence_frag], [system_sequence], [first_out_of_order_sequence], [last_out_of_order_sequence], [last_out_of_order_frag], [is_system], [priority] FROM sys.conversation_endpoints
Sql 2012
SELECT [conversation_handle], [conversation_id], [is_initiator], [service_contract_id], [conversation_group_id], [service_id], [lifetime], [state], [state_desc], [far_service], [far_broker_instance], [principal_id], [far_principal_id], [outbound_session_key_identifier], [inbound_session_key_identifier], [security_timestamp], [dialog_timer], [send_sequence], [last_send_tran_id], [end_dialog_sequence], [receive_sequence], [receive_sequence_frag], [system_sequence], [first_out_of_order_sequence], [last_out_of_order_sequence], [last_out_of_order_frag], [is_system], [priority] FROM sys.conversation_endpoints

Back to Top

---

sys.conversation_groups

This catalog view contains a row for each conversation group.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
conversation_group_id	•	•	•	•	uniqueidentifier	Identifier for the conversation group. Not NULLABLE.
service_id	•	•	•	•	int	Identifier of the service for conversations in this group. Not NULLABLE.
is_system	•	•	•	•	bit	Indicates whether this is a system instance or not. NULLABLE.

TSQL

Sql 2005
SELECT [conversation_group_id], [service_id], [is_system] FROM sys.conversation_groups
Sql 2008
SELECT [conversation_group_id], [service_id], [is_system] FROM sys.conversation_groups
Sql 2008 R2
SELECT [conversation_group_id], [service_id], [is_system] FROM sys.conversation_groups
Sql 2012
SELECT [conversation_group_id], [service_id], [is_system] FROM sys.conversation_groups

Back to Top

---

sys.conversation_priorities

conversation priority created in the current database, as shown in the following table:

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name		•	•	•	sysname	Name of the conversation priority. Not NULLABLE.
service_contract_id		•	•	•	int	The identifier of the contract that is specified for the conversation priority. This can be joined on the service_contract_id column in sys.service_contracts. NULLABLE.
local_service_id		•	•	•	int	The identifier of the service that is specified as the local service for the conversation priority. This column can be joined on the service_id column in sys.services. NULLABLE.
remote_service_name		•	•	•	nvarchar(256)	The name of the service that is specified as the remote service for the conversation priority. NULLABLE.
priority		•	•	•	tinyint	The priority level that is specified in this conversation priority. Not NULLABLE.
priority_id			•	•	int	A number that uniquely identifies the conversation priority. Not NULLABLE.

TSQL

Sql 2008
SELECT [name], [service_contract_id], [local_service_id], [remote_service_name], [priority] FROM sys.conversation_priorities
Sql 2008 R2
SELECT [priority_id], [name], [service_contract_id], [local_service_id], [remote_service_name], [priority] FROM sys.conversation_priorities
Sql 2012
SELECT [priority_id], [name], [service_contract_id], [local_service_id], [remote_service_name], [priority] FROM sys.conversation_priorities

Back to Top

---

sys.message_type_xml_schema_collection_usages

This catalog view returns a row for each service message type that is validated by an XML schema collection.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
message_type_id	•	•	•	•	int	The ID of the service message type. Not NULLABLE.
xml_collection_id	•	•	•	•	int	The ID of the collection containing the validating XML schema namespace. Not NULLABLE.

TSQL

Sql 2005
SELECT [message_type_id], [xml_collection_id] FROM sys.message_type_xml_schema_collection_usages
Sql 2008
SELECT [message_type_id], [xml_collection_id] FROM sys.message_type_xml_schema_collection_usages
Sql 2008 R2
SELECT [message_type_id], [xml_collection_id] FROM sys.message_type_xml_schema_collection_usages
Sql 2012
SELECT [message_type_id], [xml_collection_id] FROM sys.message_type_xml_schema_collection_usages

Back to Top

---

sys.remote_service_bindings

This catalog view contains a row per remote service binding.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Name of this remote service binding. Not NULLABLE.
remote_service_binding_id	•	•	•	•	int	ID of this remote service binding. Not NULLABLE.
principal_id	•	•	•	•	int	ID of the database principal that owns this remote service binding. NULLABLE.
remote_service_name	•	•	•	•	nvarchar(256)	Name of the remote service that this binding applies to. NULLABLE.
service_contract_id	•	•	•	•	int	ID of the contract that this binding applies to. A value of 0 is a wildcard that means this binding applies to all contracts for the service. Not NULLABLE.
remote_principal_id	•	•	•	•	int	ID for the user specified in the remote service binding. Service Broker uses a certificate owned by this user for communicating with the specified service on the specified contracts. NULLABLE.
is_anonymous_on	•	•	•	•	bit	This remote service binding uses ANONYMOUS security. The identity of the user that begins the conversation is not provided to the target service. Not NULLABLE.

TSQL

Sql 2005
SELECT [name], [remote_service_binding_id], [principal_id], [remote_service_name], [service_contract_id], [remote_principal_id], [is_anonymous_on] FROM sys.remote_service_bindings
Sql 2008
SELECT [name], [remote_service_binding_id], [principal_id], [remote_service_name], [service_contract_id], [remote_principal_id], [is_anonymous_on] FROM sys.remote_service_bindings
Sql 2008 R2
SELECT [name], [remote_service_binding_id], [principal_id], [remote_service_name], [service_contract_id], [remote_principal_id], [is_anonymous_on] FROM sys.remote_service_bindings
Sql 2012
SELECT [name], [remote_service_binding_id], [principal_id], [remote_service_name], [service_contract_id], [remote_principal_id], [is_anonymous_on] FROM sys.remote_service_bindings

Back to Top

---

sys.routes

This catalog views contains one row per route. Service Broker uses routes to locate the network address for a service.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Name of the route, unique within the database. Not NULLABLE.
route_id	•	•	•	•	int	Identifier for the route. Not NULLABLE.
principal_id	•	•	•	•	int	Identifier for the database principal that owns the route. NULLABLE.
remote_service_name	•	•	•	•	nvarchar(256)	Name of the remote service. NULLABLE.
broker_instance	•	•	•	•	nvarchar(128)	Identifier of the broker that hosts the remote service. NULLABLE.
lifetime	•	•	•	•	datetime	The date and time when the route expires. Notice that this value does not use the local time zone. Instead, the value shows the expiration time for UTC. NULLABLE.
address	•	•	•	•	nvarchar(256)	Network address to which Service Broker sends messages for the remote service. NULLABLE.
mirror_address	•	•	•	•	nvarchar(256)	Network address of the mirroring partner for the server specified in the address. NULLABLE.

TSQL

Sql 2005
SELECT [name], [route_id], [principal_id], [remote_service_name], [broker_instance], [lifetime], [address], [mirror_address] FROM sys.routes
Sql 2008
SELECT [name], [route_id], [principal_id], [remote_service_name], [broker_instance], [lifetime], [address], [mirror_address] FROM sys.routes
Sql 2008 R2
SELECT [name], [route_id], [principal_id], [remote_service_name], [broker_instance], [lifetime], [address], [mirror_address] FROM sys.routes
Sql 2012
SELECT [name], [route_id], [principal_id], [remote_service_name], [broker_instance], [lifetime], [address], [mirror_address] FROM sys.routes

Back to Top

---

sys.server_role_members

member of each fixed server role.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
role_principal_id	•	•	•	•	int	Server-Principal ID of the role.
member_principal_id	•	•	•	•	int	Server-Principal ID of the member.

TSQL

Sql 2005
SELECT [role_principal_id], [member_principal_id] FROM sys.server_role_members
Sql 2008
SELECT [role_principal_id], [member_principal_id] FROM sys.server_role_members
Sql 2008 R2
SELECT [role_principal_id], [member_principal_id] FROM sys.server_role_members
Sql 2012
SELECT [role_principal_id], [member_principal_id] FROM sys.server_role_members

Back to Top

---

sys.service_contract_message_usages

This catalog view contains a row per (contract, message type) pair.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
service_contract_id	•	•	•	•	int	Identifier of the contract using the message type. Not NULLABLE.
message_type_id	•	•	•	•	int	Identifier of the message type used by the contract. Not NULLABLE.
is_sent_by_initiator	•	•	•	•	bit	Message type can be sent by the conversation initiator. Not NULLABLE.
is_sent_by_target	•	•	•	•	bit	Message type can be sent by the conversation target. Not NULLABLE.

TSQL

Sql 2005
SELECT [service_contract_id], [message_type_id], [is_sent_by_initiator], [is_sent_by_target] FROM sys.service_contract_message_usages
Sql 2008
SELECT [service_contract_id], [message_type_id], [is_sent_by_initiator], [is_sent_by_target] FROM sys.service_contract_message_usages
Sql 2008 R2
SELECT [service_contract_id], [message_type_id], [is_sent_by_initiator], [is_sent_by_target] FROM sys.service_contract_message_usages
Sql 2012
SELECT [service_contract_id], [message_type_id], [is_sent_by_initiator], [is_sent_by_target] FROM sys.service_contract_message_usages

Back to Top

---

sys.service_contract_usages

This catalog view contains a row per (service, contract) pair.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
service_id	•	•	•	•	int	Identifier of the service using the contract. Not NULLABLE.
service_contract_id	•	•	•	•	int	Identifier of the contract used by the service. Not NULLABLE.

TSQL

Sql 2005
SELECT [service_id], [service_contract_id] FROM sys.service_contract_usages
Sql 2008
SELECT [service_id], [service_contract_id] FROM sys.service_contract_usages
Sql 2008 R2
SELECT [service_id], [service_contract_id] FROM sys.service_contract_usages
Sql 2012
SELECT [service_id], [service_contract_id] FROM sys.service_contract_usages

Back to Top

---

sys.service_contracts

This catalog view contains a row for each contract in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Name of the contract, unique within the database. Not NULLABLE.
service_contract_id	•	•	•	•	int	Identifier of the contract. Not NULLABLE.
principal_id	•	•	•	•	int	Identifier for the database principal that owns this contract. NULLABLE.

TSQL

Sql 2005
SELECT [name], [service_contract_id], [principal_id] FROM sys.service_contracts
Sql 2008
SELECT [name], [service_contract_id], [principal_id] FROM sys.service_contracts
Sql 2008 R2
SELECT [name], [service_contract_id], [principal_id] FROM sys.service_contracts
Sql 2012
SELECT [name], [service_contract_id], [principal_id] FROM sys.service_contracts

Back to Top

---

sys.service_message_types

This catalog view contains a row per message type registered in the service broker.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Name of message type, unique within the database. Not NULLABLE.
message_type_id	•	•	•	•	int	Identifier of the message type, unique within the database. Not NULLABLE.
principal_id	•	•	•	•	int	Identifier for the database principal that owns this message type. NULLABLE.
validation	•	•	•	•	char(2)	Validation done by Broker prior to sending messages of this type. One of: N = None X = XML E = Empty Not NULLABLE.
validation_desc	•	•	•	•	nvarchar(60)	Description of the validation done by Broker prior to sending messages of this type. One of: NONE XML EMPTY NULLABLE.
xml_collection_id	•	•	•	•	int	For validation that uses an XML schema, the identifier for the schema collection used. Otherwise, NULL.

TSQL

Sql 2005
SELECT [name], [message_type_id], [principal_id], [validation], [validation_desc], [xml_collection_id] FROM sys.service_message_types
Sql 2008
SELECT [name], [message_type_id], [principal_id], [validation], [validation_desc], [xml_collection_id] FROM sys.service_message_types
Sql 2008 R2
SELECT [name], [message_type_id], [principal_id], [validation], [validation_desc], [xml_collection_id] FROM sys.service_message_types
Sql 2012
SELECT [name], [message_type_id], [principal_id], [validation], [validation_desc], [xml_collection_id] FROM sys.service_message_types

Back to Top

---

sys.service_queue_usages

This catalog view returns a row for each reference between service and service queue. A service can only be associated with one queue. A queue can be associated with multiple services.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
service_id	•	•	•	•	int	Identifier of the service. Unique within the database. Not NULLABLE.
service_queue_id	•	•	•	•	int	Identifier of the service queue used by the service. Not NULLABLE.

TSQL

Sql 2005
SELECT [service_id], [service_queue_id] FROM sys.service_queue_usages
Sql 2008
SELECT [service_id], [service_queue_id] FROM sys.service_queue_usages
Sql 2008 R2
SELECT [service_id], [service_queue_id] FROM sys.service_queue_usages
Sql 2012
SELECT [service_id], [service_queue_id] FROM sys.service_queue_usages

Back to Top

---

sys.service_queues

object in the database that is a service queue, with sys.objects.type = SQ.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Inherited From	Description
name	•	•	•	•	sysname	sys.objects	Object name.
object_id	•	•	•	•	int	sys.objects	Object identification number. Is unique within a database.
principal_id	•	•	•	•	int	sys.objects	ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint
schema_id	•	•	•	•	int	sys.objects	ID of the schema that the object is contained in. For all schema-scoped system objects that ship with SQL Server 2005, this value will always be IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA')).
parent_object_id	•	•	•	•	int	sys.objects	ID of the object to which this object belongs. 0 = Not a child object.
type	•	•	•	•	char(2)	sys.objects	Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint P = SQL stored procedure PC = Assembly (CLR) stored procedure FN = SQL scalar function FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TR = SQL DML trigger IF = SQL inline table-valued function TF = SQL table-valued-function U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure IT = Internal table
type_desc	•	•	•	•	nvarchar(60)	sys.objects	Description of the object type. AGGREGATE_FUNCTION CHECK_CONSTRAINT DEFAULT_CONSTRAINT FOREIGN_KEY_CONSTRAINT PRIMARY_KEY_CONSTRAINT SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE SQL_SCALAR_FUNCTION CLR_SCALAR_FUNCTION CLR_TABLE_VALUED_FUNCTION RULE REPLICATION_FILTER_PROCEDURE SYSTEM_TABLE SYNONYM SERVICE_QUEUE CLR_TRIGGER SQL_TRIGGER SQL_INLINE_TABLE_VALUED_FUNCTION SQL_TABLE_VALUED_FUNCTION USER_TABLE UNIQUE_CONSTRAINT VIEW EXTENDED_STORED_PROCEDURE INTERNAL_TABLE
create_date	•	•	•	•	datetime	sys.objects	Date the object was created.
modify_date	•	•	•	•	datetime	sys.objects	Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shipped	•	•	•	•	bit	sys.objects	Object is created by an internal SQL Server component.
is_published	•	•	•	•	bit	sys.objects	Object is published.
is_schema_published	•	•	•	•	bit	sys.objects	Only the schema of the object is published.
max_readers	•	•	•	•	smallint		Maximum number of the concurrent readers allowed in the queue.
activation_procedure	•	•	•	•	nvarchar(776)		Three-part name of the activation procedure.
execute_as_principal_id	•	•	•	•	int		ID of the EXECUTE AS database principal. NULL by default or if EXECUTE AS CALLER. ID of the specified principal if EXECUTE AS SELF EXECUTE AS . -2 = EXECUTE AS OWNER.
is_activation_enabled	•	•	•	•	bit		1 = Activation is enabled.
is_receive_enabled	•	•	•	•	bit		1 = Receive is enabled.
is_enqueue_enabled	•	•	•	•	bit		1 = Enqueue is enabled.
is_retention_enabled	•	•	•	•	bit		1 = Messages are retained until dialog end.
is_poison_message_handling_enabled			•	•	bit		1 = poison message handling is enabled in the queue.

TSQL

Sql 2005
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [max_readers], [activation_procedure], [execute_as_principal_id], [is_activation_enabled], [is_receive_enabled], [is_enqueue_enabled], [is_retention_enabled] FROM sys.service_queues
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [max_readers], [activation_procedure], [execute_as_principal_id], [is_activation_enabled], [is_receive_enabled], [is_enqueue_enabled], [is_retention_enabled] FROM sys.service_queues
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [max_readers], [activation_procedure], [execute_as_principal_id], [is_activation_enabled], [is_receive_enabled], [is_enqueue_enabled], [is_poison_message_handling_enabled], [is_retention_enabled] FROM sys.service_queues
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [max_readers], [activation_procedure], [execute_as_principal_id], [is_activation_enabled], [is_receive_enabled], [is_enqueue_enabled], [is_retention_enabled], [is_poison_message_handling_enabled] FROM sys.service_queues

Back to Top

---

sys.services

This catalog view contains a row for each service in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
name	•	•	•	•	sysname	Case-sensitive name of service, unique within the database. Not NULLABLE.
service_id	•	•	•	•	int	Identifier of the service. Not NULLABLE.
principal_id	•	•	•	•	int	Identifier for the database principal that owns this service. NULLABLE.
service_queue_id	•	•	•	•	int	Object id for the queue that this service uses. Not NULLABLE.

TSQL

Sql 2005
SELECT [name], [service_id], [principal_id], [service_queue_id] FROM sys.services
Sql 2008
SELECT [name], [service_id], [principal_id], [service_queue_id] FROM sys.services
Sql 2008 R2
SELECT [name], [service_id], [principal_id], [service_queue_id] FROM sys.services
Sql 2012
SELECT [name], [service_id], [principal_id], [service_queue_id] FROM sys.services

Back to Top

---

sys.transmission_queue

This catalog view contains a row for each message in the transmission queue, as shown in the following table:

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
conversation_handle	•	•	•	•	uniqueidentifier	Identifier for the conversation that this message belongs to. Not NULLABLE.
to_service_name	•	•	•	•	nvarchar(256)	Name of the service that this message is to. NULLABLE.
to_broker_instance	•	•	•	•	nvarchar(128)	Identifier of the broker that hosts the service that this message is to. NULLABLE.
from_service_name	•	•	•	•	nvarchar(256)	Name of the service that this message is from. NULLABLE.
service_contract_name	•	•	•	•	nvarchar(256)	Name of the contract that the conversation for this message follows. NULLABLE.
enqueue_time	•	•	•	•	datetime	Time at which the message entered the queue. This value uses UTC regardless of the local time zone for the instance. Not NULLABLE.
message_sequence_number	•	•	•	•	bigint	Sequence number of the message. Not NULLABLE.
message_type_name	•	•	•	•	nvarchar(256)	Message type name for the message. NULLABLE.
is_conversation_error	•	•	•	•	bit	Whether this message is an error message. 0 = Not an error message. 1 = Error message. Not NULLABLE.
is_end_of_dialog	•	•	•	•	bit	Whether this message is an end of conversation message. Not NULLABLE. 0 = Not an end of conversation message. 1 = End of conversation message. Not NULLABLE.
message_body	•	•	•	•	varbinary(max)	The body of this message. NULLABLE.
transmission_status	•	•	•	•	nvarchar(4000)	The reason this message is on the queue. This is generally an error message explaining why sending the message failed. If this is blank, the message hasn€™t been sent yet. NULLABLE.
priority		•	•	•	tinyint	The priority level that is assigned to this message. Not NULLABLE.

TSQL

Sql 2005
SELECT [conversation_handle], [to_service_name], [to_broker_instance], [from_service_name], [service_contract_name], [enqueue_time], [message_sequence_number], [message_type_name], [is_conversation_error], [is_end_of_dialog], [message_body], [transmission_status] FROM sys.transmission_queue
Sql 2008
SELECT [conversation_handle], [to_service_name], [to_broker_instance], [from_service_name], [service_contract_name], [enqueue_time], [message_sequence_number], [message_type_name], [is_conversation_error], [is_end_of_dialog], [message_body], [transmission_status], [priority] FROM sys.transmission_queue
Sql 2008 R2
SELECT [conversation_handle], [to_service_name], [to_broker_instance], [from_service_name], [service_contract_name], [enqueue_time], [message_sequence_number], [message_type_name], [is_conversation_error], [is_end_of_dialog], [message_body], [transmission_status], [priority] FROM sys.transmission_queue
Sql 2012
SELECT [conversation_handle], [to_service_name], [to_broker_instance], [from_service_name], [service_contract_name], [enqueue_time], [message_sequence_number], [message_type_name], [is_conversation_error], [is_end_of_dialog], [message_body], [transmission_status], [priority] FROM sys.transmission_queue

Back to Top

---