Azure SQL Database中Sql Dependency无法运行的问题排查与解决

你遇到的Statement 'RECEIVE MSG' is not supported错误，是因为Azure SQL Database对传统SqlDependency依赖的Service Broker功能有部分限制，和本地SQL Server的支持不完全一致。下面是一步步的解决办法，帮你在Azure环境中正常运行这个服务：

1. 先确认基础配置和权限 #

• 检查Service Broker状态：在Azure SQL Database中执行以下查询，确保返回值为1：

  SELECT is_broker_enabled FROM sys.databases WHERE name = 'TestSQLDependendcy';
  

  如果没启用，执行这条语句开启（需要db_owner权限）：

  ALTER DATABASE TestSQLDependendcy SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
  

• 给用户授权必要权限：Azure SQL需要额外的权限才能使用查询通知，执行：

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [你的数据库用户名];
  GRANT SELECT ON TestNombre TO [你的数据库用户名];
  

2. 修复代码中的明显问题 #

你的代码里有个致命错误：cmd.Dispose();在创建SqlCommand后立刻释放了它，导致后续无法使用这个命令。另外还有一些可以优化的地方，修改后的代码如下：

public class Program
{
    // 统一管理连接字符串，避免重复编写
    private const string DbConnectionString = "Server=tcp:xxxxx.database.windows.net,0000;Initial Catalog=TestSQLDependendcy;Persist Security Info=False;User ID=xxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

    static void Main(string[] args)
    {
        // 权限验证需要调用Demand()才能生效
        SqlClientPermission permission = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
        try
        {
            permission.Demand();
        }
        catch (SecurityException)
        {
            Console.WriteLine("No tienes permitido solicitar notificaciones");
            return;
        }

        try
        {
            SqlDependency.Start(DbConnectionString);
            Console.WriteLine("Empezando a escuchar");
            GetAlerts();
            Console.WriteLine("Presiona enter para salir");
            Console.ReadLine();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error inicial: {ex.Message}");
        }
        finally
        {
            Console.WriteLine("Deteniendo la escucha...");
            SqlDependency.Stop(DbConnectionString);
        }
    }

    public static void GetAlerts()
    {
        try
        {
            using (SqlConnection con = new SqlConnection(DbConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT Correo, Nombre FROM TestNombre", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Notification = null;

                    // 绑定SqlDependency和变更事件
                    SqlDependency dependency = new SqlDependency(cmd);
                    dependency.OnChange += OnDataChange;

                    con.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            // 处理读取到的数据，比如发送邮件
                            string correo = dr["Correo"].ToString();
                            string nombre = dr["Nombre"].ToString();
                            Console.WriteLine($"Detectado registro: {nombre} <{correo}>");
                            // 这里放你的MailMessage发送逻辑
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error al escuchar cambios: {ex.Message}");
        }
    }

    private static void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine($"Notificación recibida: Tipo={e.Type}, Info={e.Info}, Fuente={e.Source}");
        
        // 解绑事件并重新注册监听
        SqlDependency dependency = sender as SqlDependency;
        dependency?.OnChange -= OnDataChange;
        GetAlerts();
    }
}

3. 适配Azure SQL的查询要求 #

Azure SQL对用于SqlDependency的查询有严格要求，必须是确定性查询，否则会触发失败：

• 必须明确指定列名（不能用SELECT *）
• 必须引用完整的表名（不能用别名）
• 目标表必须有主键（这是Query Notifications的硬性要求）
• 不能使用不确定函数，比如GETDATE()、RAND()（除非作为常量条件）

你的查询SELECT Correo, Nombre FROM TestNombre是符合要求的，只要TestNombre表有主键就没问题。

4. 备选方案：如果SqlDependency还是不稳定 #

如果上述调整后还是有问题，推荐使用Azure SQL原生的变更追踪（Change Tracking），它在Azure环境中更可靠，没有Service Broker的限制：

1. 先开启数据库和表的变更追踪：

   ALTER DATABASE TestSQLDependendcy SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
   ALTER TABLE TestNombre ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
   

2. 在代码中定期查询变更的数据，比如：

   SELECT Correo, Nombre FROM TestNombre 
   JOIN CHANGETABLE(CHANGES TestNombre, @LastSyncVersion) ct ON TestNombre.Id = ct.Id;
   

   这种方式不需要依赖SqlDependency，通过轮询或定时任务就能检测数据变化，在Azure中更稳定。

内容的提问来源于stack exchange，提问作者Isaías Orozco Toledo