SELECT 'Checking Broker Service Status...'
IF (select Top 1 is_broker_enabled from sys.databases where name = 'NWMESSAGE')=1
    SELECT ' Broker Service IS Enabled'  -- Should return a 1.
ELSE
    SELECT '** Broker Service IS DISABLED ***'
/* If Is_Broker_enabled returns 0, uncomment and run this code
ALTER DATABASE NWMESSAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Alter Database NWMESSAGE Set enable_broker
GO
ALTER DATABASE NWDataChannel SET MULTI_USER
GO
*/

SELECT 'Checking For Disabled Queues....'
-- ensure the queues are enabled
--  0 indicates the queue is disabled.
Select '** Receive Queue Disabled: '+name from sys.service_queues where is_receive_enabled = 0
--select [name], is_receive_enabled from sys.service_queues;

/*If the queue is disabled, to enable it
alter queue QUEUENAME with status=on; – replace QUEUENAME with the name of your queue
*/

-- Get General information about the queues
--select * from sys.service_queues

-- Get the message counts in each queue
SELECT 'Checking Message Count for each Queue...'
select q.name, p.rows
from sys.objects as o
join sys.partitions as p on p.object_id = o.object_id
join sys.objects as q on o.parent_object_id = q.object_id
join sys.service_queues sq on sq.name = q.name
where p.index_id = 1

-- Ensure all the queue activiation sprocs are present
SELECT 'Checking for Activation Stored Procedures....'
SELECT  '** Missing Procedure:  '+q.name 
From sys.service_queues q
Where NOT Exists(Select * from sysobjects where xtype='p' and name='activation_'+q.name)
and q.activation_procedure is not null

DECLARE @sprocs Table (Name Varchar(2000))
Insert into @sprocs Values ('Echo')
Insert into @sprocs Values ('HTTP_POST')
Insert into @sprocs Values ('InitializeRecipients')
Insert into @sprocs Values ('sp_EnableRecipient')
Insert into @sprocs Values ('sp_ProcessReceivedMessage')
Insert into @sprocs Values ('sp_SendXmlMessage')

SELECT 'Checking for required stored procedures...'
SELECT  '** Missing Procedure:  '+s.name 
From @sprocs s
Where NOT Exists(Select * from sysobjects where xtype='p' and name=s.name)
GO
-- Check the services
Select 'Checking Recipient Message Services...'
Select '** Missing Message Service:' + r.RecipientName +'MessageService'
From Recipient r
Where not exists (Select * from sys.services s where  s.name  COLLATE SQL_Latin1_General_CP1_CI_AS= r.RecipientName+'MessageService')

DECLARE @svcs Table (Name Varchar(2000))
Insert into @svcs Values ('XmlMessageSendingService')

SELECT  '** Missing Service:  '+s.name 
From @svcs s
Where NOT Exists(Select * from sys.services where name=s.name COLLATE SQL_Latin1_General_CP1_CI_AS)
GO

/*** To Test a message send Run:
sp_SendXmlMessage  'TSQLTEST', 'CommerceEngine','<Root><Text>Test</Text></Root>'
*/

Select CAST(message_body as XML) as xml, * From XmlMessageSendingQueue

/*** clean out all queues
declare @handle uniqueidentifier

declare conv cursor for
  select conversation_handle from sys.conversation_endpoints

open conv
fetch next from conv into @handle

while @@FETCH_STATUS = 0
Begin
   END Conversation @handle with cleanup
   fetch next from conv into @handle
End

close conv
deallocate conv

***********************