- 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 2005SELECT [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 2005SELECT [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 2008SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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