Enable broker service on all databases but it check if Service Broker is enabled.
1
2-- ***************************************** --
3-- SQL Script to ENABLE_BROKER
4-- ***************************************** --
5--
6PRINT @@servername;
7PRINT Getdate();
8BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
9
10--#region Select databases to Enable Broker Service
11
12SELECT [name] INTO #dbs
13FROM sys.databases
14WHERE
15 database_id > 4
16 AND is_broker_enabled = 0 -- check if Service Broker is enabled.
17 AND name NOT IN ('ReportServer','ReportServerTempDB')
18
19--#endregion Select databases to Enable Broker Service
20
21USE [MASTER];
22SET NOCOUNT ON;
23DECLARE @name nvarchar(200);
24DECLARE @sql NVARCHAR(MAX);
25DECLARE @ToKill AS NVARCHAR(MAX);
26
27WHILE (EXISTS(SELECT * FROM #dbs))
28BEGIN
29
30 SELECT TOP 1 @name = name
31 FROM #dbs;
32 DELETE FROM #dbs
33 WHERE
34 @name = name;
35
36 DECLARE @msg nvarchar(200) = '-- SET ENABLE_BROKER Database : ' + @name;
37 RAISERROR (@msg, 10, 1) WITH NOWAIT;
38
39 SET @ToKill = '';
40 SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
41 FROM master..sysprocesses
42 WHERE dbid = DB_ID(@name);
43
44 RAISERROR (@tokill, 10, 1) WITH NOWAIT;
45 EXEC (@tokill);
46
47 SET @sql = 'ALTER DATABASE [' + @name + '] SET ENABLE_BROKER WITH NO_WAIT;'
48
49 RAISERROR (@sql, 10, 1) WITH NOWAIT;
50 EXEC (@sql);
51
52END
Comments