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','Test'
*/
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
***********************