/****************************************************************************

  启动Broker并设置兼容性

  刘志林

  2017-11-14

  http://www.cnblogs.com/lzl_17948876/

  lzl_17948876@hotmail.com

****************************************************************************/

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;