Abilitare il broker su di un database

Ecco lo script per abilitare il broker su un database di SQL server

declare @DatabaseName nvarchar(1000)
set @DatabaseName = 'myDatabase'

if((select is_broker_enabled from sys.databases WHERE name = @DatabaseName) = 0)
begin
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid =
(SELECT dbid FROM master.dbo.sysdatabases
WHERE name = @DatabaseName)
DECLARE @SysProcId smallint
OPEN SysProc --kill all the processes running against the database
FETCH NEXT FROM SysProc INTO @SysProcId
DECLARE @KillStatement char(30)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
EXEC (@KillStatement)
FETCH NEXT FROM SysProc INTO @SysProcId
END
WAITFOR DELAY '000:00:01'
ALTER DATABASE IBCMS_bookingManager SET ENABLE_BROKER
end


E questo è quello che serve per disabilitarlo:

declare @DatabaseName nvarchar(1000)
set @DatabaseName = 'myDatabase'

if((select is_broker_enabled from sys.databases WHERE name = @DatabaseName) = 1)
begin
print @DatabaseName + ' has broker enabled. Start disabling process....'
print 'Initial status of ' + @DatabaseName +': '+ convert(nvarchar(max), DATABASEPROPERTYEX(@DatabaseName, 'Status')) + ' ...'

/*
exec('Alter database '+ @DatabaseName +' set offline')
select DATABASEPROPERTYEX(@DatabaseName, 'Status')
exec('Alter database '+ @DatabaseName +' set online')
select DATABASEPROPERTYEX(@DatabaseName, 'Status')
*/

DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid =
(SELECT dbid FROM master.dbo.sysdatabases
WHERE name = @DatabaseName)
DECLARE @SysProcId smallint
OPEN SysProc --kill all the processes running against the database
FETCH NEXT FROM SysProc INTO @SysProcId
DECLARE @KillStatement char(30)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
EXEC (@KillStatement)
FETCH NEXT FROM SysProc INTO @SysProcId
END
WAITFOR DELAY '000:00:01'
ALTER DATABASE IBCMS_BookingManager SET DISABLE_BROKER
print 'Now ' + @DatabaseName + ' has broker disabled'

end
else
print @DatabaseName + ' has broker already disabled'