Using SQL Dependency with Azure

Question

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.

This is the error:

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

This is my code:

     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();
    }
  }

How to run this service in Azure SQL Database?

Answer 1

Currently, Azure SQL does not support Service Broker.

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

In addition, this documentation https://learn.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.