SqlDependency 监控数据库 无效问题汇总

首先启用BROKER

SELECT is_broker_enabled FROM sys.databases WHERE name = 'ATSHOW' 

use ATSHOW
go

ALTER DATABASE ATSHOW SET NEW_BROKER WITH ROLLBACK IMMEDIATE; 

ALTER DATABASE ATSHOW SET ENABLE_BROKER; 

SELECT is_broker_enabled FROM sys.databases WHERE name = 'ATSHOW' 
 

 

参考程序

private static string conn = ConfigurationManager.ConnectionStrings["连接字符串名称"].ConnectionString;
    static SqlDependency dependency;
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlDependency.Start(conn); //传入连接字符串,启动基于数据库的监听
        if (!IsPostBack)
        {
            Update(conn);
        }
    }

    //使用SqlDependency监控数据库表变化
    private void Update(string conn)
    {
        using (SqlConnection connection = new SqlConnection(conn))
        {
            StringBuilder strsql = new StringBuilder();
            //对被监控的目标表做简单查询，此处 要注意 不能使用* 表名要加[dbo] 否则会出现一直调用执行 OnChange
            strsql.Append(@"sql查询语句，查询目标表");            
            using (SqlCommand command = new SqlCommand(strsql.ToString(), connection))
            {
                connection.Open();
                command.CommandType = CommandType.Text;
                dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);//添加监控，检测数据表变化
                //必须要执行一下command
                command.ExecuteNonQuery();
                //Console.WriteLine(dependency.HasChanges);
                //connection.Close();
            }
        }
        
    }

    //检测到数据表变化后执行动作
    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            //这里要再次调用
            Update(conn);

            //刷新前台页面
            Response.AddHeader("Refresh", "0");
        }

    }

 

 

如果无效，重要问题

一、检查SQL查询语句

查询通知只支持某些 Transact-SQL 语句。

首先，要支持通知，查询中不得包含下列内容：

• 派生的表。

• 行集函数。

• UNION 运算符。

• 子查询。

• 外联接或自联接。

• TOP 子句。

• DISTINCT 关键字。

• COUNT(*) 聚合函数。

• AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数。

• 用户定义的聚合函数。

• 引用可空表达式的 SUM 函数。

• 完全文本谓词 CONTAINS 或 FREETEXT。

• COMPUTE 或 COMPUTE BY 子句。

• 聚合表达式（如果在选择列表中未指定 GROUP BY）。如果指定了 GROUP BY，选择列表中必须包含 COUNT_BIG(*) 表达式，并且不能指定 HAVING、CUBE 或 ROLLUP。

• INTO 子句。

• 将阻止结果更改的条件（例如 WHERE 1=0）。

• FOR BROWSE（或在 SET NO_BROWSETABLE ON 的情况下运行）。

• READPAST 锁定提示。

其次，查询不得引用下列内容：

• 临时表或表变量。

• 其他数据库或服务器中的表或视图。

• 所有其他视图或表值函数。

• 任何系统表或视图。

• 任何非确定性函数，包括评级和窗口函数。

• 任何服务器全局变量

• 任何服务中介程序队列。

• 同义词。

最后，查询必须引用基表或视图。

 

二、必须调用 command.ExecuteNonQuery(); 参考上面代码

三、有可能字符串连接中的登陆用户的权限不足

首先检查所有着是否正确

​

用图形化的方法。

数据库右击->属性->文件->选择数据库所有者->选择[sa]-- ok。

 

四、其他问题，对于其他问题，使用 SQL 跟踪对查询通知进行故障排除

事件类

 

以下事件类报告有关查询通知的信息：

• QN:Dynamics

  QN:Dynamics 事件报告有关数据库引擎执行以支持查询通知的后台活动的信息。在数据库引擎中，后台线程监视订阅超时、要激发的挂起订阅和参数表析构。

• QN:Parameter Table

  QN:Parameter Table 事件报告有关创建、删除存储参数信息的内部表和为其保留引用计数所需操作的信息。另外，此事件还报告重置参数表的使用计数的内部活动。

• QN:Template

  QN:Template 事件报告有关查询模板的内部使用情况的信息。查询模板是数据库引擎用于针对通知共享查询定义的机制。这些模板是与参数表一起创建的。当创建、使用或销毁查询模板时，数据库引擎会创建此类型的事件。

• QN:Subscription

  QN:Subscription 事件报告有关通知订阅的信息。例如，在注册、删除或破坏订阅时，此事件报告有关支持查询通知订阅的 Service Broker 操作的信息。

使用方法：使用SQL SERVER Profiler监视消息，显示所有事件，然后勾选Broker相关事件，即可查看相关消息；