在Azure中使用SQL依赖关系 [英] Using SQL Dependency with Azure

问题描述

在我的本地数据库中，Sql Dependency正常运行，但是当我迁移到Azure数据库时无法正常工作.

In my Local DB Sql Dependency works fine, but when i migrate to an Azure Database is not work.

我检查服务代理是否已启用，并且已激活.

I check if service broker is enabled, and it is activated.

这是错误:

此版本的SQL Server不支持声明"RECEIVE MSG"

Statement 'RECEIVE MSG' is not supported in this version of SQL Server

这是我的代码:

     public class Program
  {
    static void Main(string[] args)
    {
      SqlClientPermission permission = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);

      if (SolicitarNotifications())
      {
        string con = "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;";
        SqlConnection conn = new SqlConnection(con);
        SqlDependency.Start(con);
        Console.WriteLine("Empezando a escuchar");
        GetAlerts();

        Console.WriteLine("Presiona enter para salir");
        Console.ReadLine();

        Console.WriteLine("Deteniendo la escucha...");
        SqlDependency.Stop(con);
      }
      else {
        Console.WriteLine("No tienes permitido solicitar notificaciones");
      }
    }

    public static void GetAlerts()
    {
      try
      {
        using (SqlConnection con = new SqlConnection("Server=tcp:xxxxx.database.windows.net,0000;Initial Catalog=TestSQLDependendcy;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
        {
          SqlCommand cmd = new SqlCommand("Select Correo, Nombre From TestNombre", con);
          cmd.CommandType = CommandType.Text;
          cmd.Notification = null;
          cmd.Dispose();
          SqlDependency dependency = new SqlDependency(cmd);
          dependency.OnChange += new OnChangeEventHandler(OnDataChange);
          con.Open();
          SqlDataReader dr = cmd.ExecuteReader();
          {
            while (dr.Read())
            {
              if (dr.HasRows)
              {
                using (MailMessage mm = new MailMessage())
                {
                      //Send Mails
                }
              }
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message.ToString());
      }
    }

    private static void OnDataChange(object sender, SqlNotificationEventArgs e)
    {

      SqlDependency dependency = sender as SqlDependency;
      dependency.OnChange -= new OnChangeEventHandler(OnDataChange);
      GetAlerts();
    }
  }

如何在Azure SQL数据库中运行此服务?

How to run this service in Azure SQL Database?

推荐答案

当前，Azure SQL不支持Service Broker.

Currently, Azure SQL does not support Service Broker.

在 https://docs.microsoft.com/zh-cn/sql/t-sql/statements/receive-transact-sql?view=sql-server-2017 ，其中指出了Azure SQL不支持它.

Find RECEIVE statement supported versions at https://docs.microsoft.com/en-us/sql/t-sql/statements/receive-transact-sql?view=sql-server-2017, which states Azure SQL does not support it.

此外，本文档 https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-features 在SQL Server，Azure SQL和托管实例之间提供了很棒的功能比较文档.请注意，管理实例在某些方面支持Service Broker.我建议注册并尝试一下.

In addition, this documentation https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features provides a great feature comparison documentation between SQL Server, Azure SQL and Managed Instances. Note that Manage Instance supports Service Broker with some differences. I'd recommend signing up for the preview and trying it out.

这篇关于在Azure中使用SQL依赖关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！