Follow

How do I enable the disabled SQL broker queues

PROBLEM:

On the perftest.asp page, I see that some of my SQL Broker queues are disabled. How do I enable the disabled SQL broker queues?

 

SOLUTION:

Run the following query in SQL to re-enable the SQL broker queues on your system.

USE ksubscribers
DECLARE @queueName VARCHAR(255)
DECLARE @dcommand VARCHAR(255)
DECLARE vend_cursor CURSOR
FOR 

select name from sys.service_queues where is_receive_enabled = 0 and is_enqueue_enabled = 0
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO @queueName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dcommand = 'ALTER QUEUE ' + @queueName + ' with status = on' 
EXEC(@dcommand)
FETCH NEXT FROM vend_cursor
INTO @queueName

END
CLOSE vend_cursor
DEALLOCATE vend_cursor
SET @dcommand = 'ALTER DATABASE [ksubscribers] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;'
EXEC(@dcommand) 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.