SSB_SHELL_Configure.sql 7.74 KB
if exists (select * from sys.objects WHERE object_id = object_id('SSB_SHELL_Configure'))
BEGIN
	DROP PROC SSB_SHELL_Configure
END
GO

CREATE PROCEDURE dbo.SSB_SHELL_Configure
	(
		@Option varchar(500) 
	)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
	--this proc ensures that all aspects of SHELL Queueing are in place and running as expected. 
	
	DECLARE @dbname varchar(200)
	SELECT @dbname = DB_NAME()
	DECLARE @exec_str varchar(max);

IF @Option NOT IN ('SETUP','TEARDOWN','TEARDOWN FORCE OVERRIDE','DISABLE ALL QUEUES','STALLED MESSAGE CLEANOUT')
BEGIN
	PRINT 'Valid @Option:  SETUP,TEARDOWN,TEARDOWN FORCE OVERRIDE,CHECK,BROKER HELP,DISABLE ALL QUEUES'
	PRINT '     SETUP						= setup/upgrade and run the reasonability checks. '
	PRINT '     TEARDOWN					= remove all traces of SHELL queueing, but only if the Qs are clear.  Leaves the table triggers. '
	PRINT '     TEARDOWN FORCE OVERRIDE		= remove all traces of SHELL queueing, regardless.'
	PRINT '     DISABLE ALL QUEUES			= disables all activated Qs and tickling.'
	PRINT '     STALLED MESSAGE CLEANOUT    = cleans out Service Broker messages that are in-flight but damaged.  This should only be run if you know what you are doing, after you fix a broken Q'
	RAISERROR ('You picked an invalid @Option.',16,1)
	RETURN 1
END;	


------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--DISABLE and TEARDOWN section
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------	

IF @Option IN ('DISABLE ALL QUEUES', 'TEARDOWN', 'TEARDOWN FORCE OVERRIDE') 
BEGIN

	--get all activated Qs and process
	DECLARE @text varchar(max)
	DECLARE Squirrel CURSOR FOR 
		SELECT name 
		FROM sys.service_queues q
		WHERE q.is_activation_enabled = 1 
		AND name like 'SHELL%'
	OPEN Squirrel
	FETCH NEXT FROM Squirrel INTO @text 
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		EXEC ('ALTER QUEUE ' + @text + ' WITH ACTIVATION (STATUS = OFF);')
		FETCH NEXT FROM Squirrel INTO @text
	END
	CLOSE Squirrel
	DEALLOCATE Squirrel;
END;

	
IF @Option IN ('TEARDOWN', 'TEARDOWN FORCE OVERRIDE') 
BEGIN
	--we don't turn off Service Broker and we don't DROP any of the activation procedures/sub-procs.
	--we handle these things in dependent order (drop the bound service before the Q, etc)
	
	DECLARE @Warnings int
	SELECT @Warnings = 0 
	
	--check to see if there is unprocessed data in any of the Qs and warn accordingly.
	IF EXISTS (select * from sys.service_queues WHERE name LIKE 'SHELL%')
	BEGIN
		IF EXISTS (select * from [SHELLQ])
		BEGIN
			SELECT @Warnings = 1
		END;
	END;

	IF EXISTS (select * from sys.transmission_queue)
	BEGIN
		SELECT @Warnings = 1
	END

	--now check if an activator is running.  If so we need to fail.  
	IF EXISTS (
		SELECT act.spid, act.procedure_name
		FROM sys.dm_broker_activated_tasks act
		JOIN sys.service_queues q 
			ON act.queue_id = q.object_id
		JOIN sys.services s 
			ON q.object_id = s.service_queue_id
		WHERE s.name = 'SHELLSvc'
		AND act.database_id = db_id()
	)
	BEGIN
		SELECT @Warnings = 1
	END;

	IF (@Warnings = 1) AND (@Option = 'TEARDOWN FORCE OVERRIDE') 
	BEGIN
		RAISERROR ('One or more Qs has unprocessed data.  The queue needs to drain before it can be torn down.  Please wait 1 minute and retry this command.',16,1)
		RETURN 1
	END;

	--DROP Objects
	IF EXISTS (select * from sys.services WHERE name = 'SHELLSvc')
	BEGIN
		DROP SERVICE [SHELLSvc]
	END;
	
	IF EXISTS (select * from sys.service_queues WHERE name = 'SHELLQ')
	BEGIN
		DROP QUEUE [SHELLQ]
	END;
	
	
	
	--general SB cleanup stuff
	IF EXISTS (select * from sys.conversation_endpoints WHERE far_service IN ('SHELLSvc') AND state_desc <> 'CLOSED')
	BEGIN
		RAISERROR ('sys.conversation_endpoints has non-CLOSED conversations that will need to be ENDed with CLEANUP.',16,1)
	END;
	
	--cleanup the transmission q
	IF EXISTS (select * from sys.transmission_queue)
	BEGIN
		RAISERROR ('sys.transmission_queue has non-CLOSED conversations that will need to be ENDed with CLEANUP.',16,1)
		--END CONVERSATION '260929FB-8B5A-E211-B928-00215E365062' WITH CLEANUP;
	END;
	
	RETURN 0 
END;  --TEARDOWN


------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--General Setup Section
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------

IF @Option IN ('SETUP')
BEGIN

	--add Message Types here, if needed

	--add contracts here, if needed 

	--is the SHELL Q and service installed and running? 
	IF NOT EXISTS (select * from sys.service_queues WHERE name = 'SHELLQ')
	BEGIN
		CREATE QUEUE [SHELLQ];
	END;
	
	IF NOT EXISTS (select * from sys.services WHERE name = 'SHELLSvc')
	BEGIN
		CREATE SERVICE [SHELLSvc] ON QUEUE [SHELLQ] ([DEFAULT]);
	END;
	
	
	--create activator here, if desired.  
	--or create a "shell" activator temporarily.  We need the activator to enable activation on the Q
	
	--is the Activator procedure installed?
	IF NOT EXISTS (select * from sys.objects WHERE name = 'SHELLQueueTicklerActivator')
	BEGIN
		PRINT 'Creating the SHELLQueueTicklerActivator'
		--we only build a shell so we don't accidentally break the Queue by dropping the Activator
		--ALTERing later will not break a Q 
		EXEC ('CREATE PROCEDURE SHELLQueueTicklerActivator AS BEGIN DECLARE @i INT END;');
	END;
	

	--is activation enabled on the Q?
	--for internal activation attach it to the service queue. 
	IF EXISTS (select * from sys.service_queues WHERE name = 'SHELLTicklerQ' AND is_activation_enabled = 0)
	BEGIN 
		ALTER QUEUE [SHELLTicklerQ]
			WITH ACTIVATION 
				(
					procedure_name = dbo.SHELLQueueTicklerActivator
					, max_queue_readers = 5  --set this to the amount of "concurrency" desired
					, EXECUTE AS OWNER
					, STATUS = ON
				);
	END
		
END;  --SETUP

IF @Option = 'STALLED MESSAGE CLEANOUT'
BEGIN
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--cleans out messages for all Q/Svc after you have fixed them
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
	declare @conv uniqueidentifier
	declare cur CURSOR FOR 
		SELECT conversation_handle
		FROM sys.conversation_endpoints
		WHERE state_desc <> 'CLOSED' 
	open cur
	fetch next from cur into @conv
	while @@fetch_status = 0
	begin
		END CONVERSATION @conv WITH CLEANUP
		fetch next from cur into @conv
	end
	close cur
	deallocate cur

END;
END;
GO
GRANT EXECUTE ON dbo.SSB_SHELL_Configure to Public;
GO