December 6, 2012

Service Broker Catalog Views

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

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

No comments:

Post a Comment

Total Pageviews