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)
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
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)