关于SystemSqlDependency使用问题总结

注意
当数据库兼容级别设置为 90 时，如果将 ANSI_WARNINGS 设置为 ON，则将使 ARITHABORT 隐式设置为 ON。如果数据库兼容级别设置为 80 或更低，则必须将 ARITHABORT 选项显式设置为 ON。如果是80或更低，会造成通知事件一直触发。
set ANSI_WARNINGS on
set ARITHABORT on

use master
ALTER DATABASE xxdb set ENABLE_BROKER
alter database xxdb set NEW_BROKER
user xxdb
exec sp_changedbowner @loginame = ‘sa’ --一定要设置

SQL Server 通过SQL脚本启动Broker并设置兼容性
/****************************************************************************
启动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;