SQL Server 通过SQL脚本启动Broker并设置兼容性

SQL Server数据库中通过SQL启用Broker 并建立相关队列和服务

兼容代码使其可以在2000库中执行不报错

针对的是2008版本, 如果是其他版本可以改相关版本号和兼容性标记

/****************************************************************************
  启动Broker并设置兼容性
  刘志林
  2017-11-14
  http://www.cnblogs.com/lzl_17948876/
  [e-mail]
****************************************************************************/
DECLARE @_DBVersion VARCHAR(10);
SELECT @_DBVersion = CAST(SERVERPROPERTY('productversion') AS VARCHAR);
DECLARE @x INT;
SET @x = CHARINDEX('.', @_DBVersion, 0);
IF CAST(LEFT(@_DBVersion, @x - 1) AS INT) >= 10 --判断是否2008或者更高版本
BEGIN
DECLARE @DBName VARCHAR(50);
DECLARE @SQL VARCHAR(1024);
SELECT @DBName = DB_NAME();
--启用Broker
IF DATABASEpRoPERTYEX(@DBName, 'IsBrokerEnabled') <> 1
BEGIN
SET @SQL = 'USE [master];'
+ 'ALTER DATABASE [' + @DBName + '] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;'
+ 'ALTER DATABASE [' + @DBName + '] SET ENABLE_BROKER;'
+ 'USE [' + @DBName +'];';
EXEC(@SQL);
END;
--设置兼容性, 如果不设置, 兼容性为2000(80)时无法使用Broker功能
SET @SQL = 'USE [master];'
+ 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 100;' --修改兼容性为2008
+ 'USE [' + @DBName +'];';
EXEC(@SQL);

--建立队列及服务
IF OBJECT_ID('Test_Queue') IS NULL
BEGIN
EXEC('CREATE QUEUE Test_Queue');
END;
IF (SELECT COUNT(*) FROM sys.services WHERE NAME = 'Test_Service') = 0
BEGIN
EXEC('CREATE SERVICE Test_Service ON QUEUE Test_Queue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])');
END;
IF (SELECT COUNT(*) FROM sys.database_principals WHERE name = 'sql_dependency_subscriber' AND type = 'R') <> 0
BEGIN
EXEC('GRANT SEND ON SERVICE::[Test_Service] TO sql_dependency_subscriber');
END;
END;