CODEWITHSUNDEEP
sql-serverservice-broker
jgauffin
jgauffin

Reputation: 101156

Query MSSQL after the queue name?

Can I do some kind of query to retreive the queue name by using a service name?

Upvotes: 2

Views: 1652

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294387

sys.services:

service_queue_id Object id for the queue that this service uses. Not NULLABLE.

Therefore something along the lines of:

SELECT name as service_name, 
   OBJECT_SCHEMA_NAME(service_queue_id) as queue_schema_name,
   OBJECT_NAME(service_queue_id) as queue_name 
FROM sys.services

Upvotes: 3

jgauffin
jgauffin

Reputation: 101156

SELECT ServiQueue.name AS 'QueueName'
FROM sys.service_contract_message_usages AS MessageUsage
INNER JOIN sys.service_contracts AS ServiceContract ON ServiceContract.service_contract_id =MessageUsage.service_contract_id
INNER JOIN sys.service_contract_usages ServContractUse ON ServContractUse.service_contract_id =ServiceContract.service_contract_id
INNER JOIN sys.services AS Servi ON Servi.service_id=ServContractUse.service_id
INNER JOIN sys.service_queue_usages AS SerQueueUse ON SerQueueUse.service_id = Servi.service_id
INNER JOIN sys.service_queues AS ServiQueue ON ServiQueue.object_id=SerQueueUse.service_queue_id
WHERE Servi.name like 'OrderDepartment'

Source: http://blog.extreme-advice.com/2013/02/15/find-service-broker-object-like-contract-message-type-service-name-queue-name-in-sql-server/

Upvotes: 0

Related Questions