Pinky Adult Film Star.com

Enable broker

Here is the script to enable the broker on a SQL server

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

if ((select from is_broker_enabled sys.databases WHERE name = @ DatabaseName) = 0) begin

SysProc DECLARE CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR DYNAMIC
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 September ENABLE_BROKER
end


And this is what you need to disable it:

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

if ((select is_broker_enabled from sys.databases WHERE name = @ DatabaseName) = 1)

begin print @ DatabaseName + 'has enabled broker. 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 September DISABLE_BROKER
print' Now '+ @ DatabaseName +' broker has disabled '

end

else print @ DatabaseName + 'Already broker has disabled'