title: SqlDependency fires immediately
body: <p>I want to use <code>SqlDependency</code> to get notifications when some datas are changed by others applications using the database.</p>
  
  <pre><code>public class DatabaseChangesNotification : IDisposable
  {
      private static string chaineDeConnexion = ConfigurationManager.ConnectionStrings["TransfertContext"].ConnectionString;
  
      private static readonly Lazy&lt;DatabaseChangesNotification&gt; _instance = new Lazy&lt;DatabaseChangesNotification&gt;(() =&gt; new DatabaseChangesNotification());
  
      private DatabaseChangesNotification()
      {
          System.Diagnostics.Trace.WriteLine("--- SqlDependency START ---");
          SqlDependency.Start(chaineDeConnexion);
      }
  
      public void Dispose()
      {
          System.Diagnostics.Trace.WriteLine("--- SqlDependency STOP ---");
          SqlDependency.Stop(chaineDeConnexion);
      }
  
      public static DatabaseChangesNotification Instance
      {
          get 
          {
              return _instance.Value; 
          }
      }
  
      public void AbonnerNotification(string requete, OnChangeEventHandler eventhandler)
      {
          using (SqlConnection connection = new SqlConnection(chaineDeConnexion))
          {
              using (SqlCommand command = new SqlCommand(requete, connection) { Notification = null }) // clear existing notifications
              {
                  connection.Open();
  
                  var sqlDependency = new SqlDependency(command);
  
                  OnChangeEventHandler delegateAutoRemove = null;
                  delegateAutoRemove = (sender, e) =&gt; {
                      var dependency = sender as SqlDependency;
                      dependency.OnChange -= delegateAutoRemove;
                      eventhandler(sender, e); 
                  };
  
                  sqlDependency.OnChange += delegateAutoRemove;
  
                  command.ExecuteNonQuery();
              }
          }
      }
  }
  </code></pre>
  
  <p>So, with a single line i can register an event handler :</p>
  
  <pre><code>DatabaseChangesNotification.Instance.AbonnerNotification(@"SELECT IdUtilisateur, Code, Nom, Prenom, NomComplet, Login, Synchroniser FROM dbo.Utilisateur", OnChanges);  
  
      public void OnChanges(object sender, SqlNotificationEventArgs e){
          System.Diagnostics.Trace.WriteLine("------------------------------ UPDATTEEEE -------------------------");
          System.Diagnostics.Trace.WriteLine("Info:   " + e.Info.ToString());
          System.Diagnostics.Trace.WriteLine("Source: " + e.Source.ToString());
          System.Diagnostics.Trace.WriteLine("Type:   " + e.Type.ToString());
  
          GlobalHost.ConnectionManager.GetHubContext&lt;TransfertClientHub&gt;().Clients.All.hello("users modified !");
          //AbonnementChanges();
      }
  </code></pre>
  
  <p>But my problem is that the notification is immediatly fired :</p>
  
  <pre><code>--- ABONNEMENT ---
  ------------------------------ UPDATTEEEE -------------------------
  Info:   Query
  Source: Statement
  Type:   Subscribe
  </code></pre>
  
  <p>That's why I commented <code>AbonnementChanges</code> in my event handler <code>OnChanges</code> (or it will loop infinitely).</p>
  
  <p>I don't know where the problem comes from because I reset the notifications (<code>{ Notification = null }</code>) and my request respect the requirements (<a href="https://msdn.microsoft.com/en-us/library/ms181122.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/ms181122.aspx</a>).</p>
  
  <p><strong>Edit :</strong> I want to add that <code>select * from sys.dm_qn_subscriptions</code> returns nothing.</p>
  
  <p><strong>Edit :</strong> It looks like it comes from database configuration, and not from my implemention, as i tried another implemention which result in the same behaviour : <a href="http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach" rel="nofollow">http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach</a> </p>
  
  <p><strong>Edit :</strong> I don't see where it comes from since i use SA which is sysadmin and have all rights, isn't it ?</p>
  
  <p><strong>Edit :</strong> I tried to define another connection to the database following this tutorial : <a href="http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen" rel="nofollow">http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen</a></p>
  
  <p>So i created 2 roles :</p>
  
  <pre><code>EXEC sp_addrole 'sql_dependency_subscriber' 
  EXEC sp_addrole 'sql_dependency_starter' 
  
  -- Permissions needed for [sql_dependency_starter]
  GRANT CREATE PROCEDURE to [sql_dependency_starter] 
  GRANT CREATE QUEUE to [sql_dependency_starter]
  GRANT CREATE SERVICE to [sql_dependency_starter]
  GRANT REFERENCES on 
  CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
    to [sql_dependency_starter] 
  GRANT VIEW DEFINITION TO [sql_dependency_starter] 
  
  -- Permissions needed for [sql_dependency_subscriber] 
  GRANT SELECT to [sql_dependency_subscriber] 
  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] 
  GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] 
  GRANT REFERENCES on 
  CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
    to [sql_dependency_subscriber] 
  </code></pre>
  
  <p>and then i added the user (<code>production</code>) to this roles :</p>
  
  <p>-- Making sure that my users are member of the correct role.</p>
  
  <pre><code>EXEC sp_addrolemember 'sql_dependency_starter', 'production'
  EXEC sp_addrolemember 'sql_dependency_subscriber', 'production'
  </code></pre>
  
  <p>But with this connection i have the same behaviour than before. Notification are fired imediatly :</p>
  
  <pre><code>------------------------------ UPDATTEEEE -------------------------
  Info:   Query
  Source: Statement
  Type:   Subscribe
  </code></pre>
  
  <p><strong>Edit :</strong> I tried with simpler requests like : <code>SELECT Nom, Prenom FROM dbo.Utilisateur</code>.
  Here are the details of the table which should be inspected :</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  SET ANSI_PADDING ON
  GO
  
  CREATE TABLE [dbo].[Utilisateur](
      [IdUtilisateur] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Utilisateur_IdUtilisateur]  DEFAULT (newid()),
      [Code] [varchar](10) NOT NULL,
      [Nom] [varchar](100) NOT NULL,
      [Prenom] [varchar](100) NULL,
      [NomComplet]  AS (([Prenom]+' ')+[Nom]),
      [Login] [varchar](50) NULL,
      [Synchroniser] [bit] NOT NULL CONSTRAINT [DF_Utilisateur_Synchroniser]  DEFAULT ((1)),
      [DATE_CREATION] [datetime] NOT NULL CONSTRAINT [DF__Utilisate__DATE___2AA1E7C7]  DEFAULT (getdate()),
      [DATE_DERNIERE_MODIF] [datetime] NOT NULL CONSTRAINT [DF__Utilisate__DATE___2B960C00]  DEFAULT (getdate()),
      [Desactive] [bit] NOT NULL CONSTRAINT [DF_Utilisateur_Desactive]  DEFAULT ((0)),
   CONSTRAINT [PK_Utilisateur] PRIMARY KEY CLUSTERED 
  (
      [IdUtilisateur] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  </code></pre>
  
  <p>As we can see there are some columns which can't be requested. That's why i don't use it.</p>
  
  <p>Now let's check with <code>SELECT Nom, Prenom FROM dbo.Utilisateur</code> : </p>
  
  <ul>
  <li>The projected columns in the SELECT statement must be explicitly
  stated, and table names must be qualified with two-part names. Notice
  that this means that all tables referenced in the statement must be
  in the same database. <strong>OK</strong></li>
  <li>The statement may not use the asterisk (<em>) or table_name.</em> syntax to
  specify columns. <strong>OK</strong></li>
  <li>The statement may not use unnamed columns or duplicate column names. <strong>OK</strong></li>
  <li>The statement must reference a base table. <strong>OK</strong></li>
  <li>The projected columns in the SELECT statement may not contain
  aggregate expressions unless the statement uses a GROUP BY
  expression. When a GROUP BY expression is provided, the select list
  may contain the aggregate functions COUNT_BIG() or SUM(). However,
  SUM() may not be specified for a nullable column. <strong>OK</strong></li>
  <li>The statement may not specify HAVING, CUBE, or ROLLUP. A projected
  column in the SELECT statement that is used as a simple expression
  must not appear more than once. <strong>OK</strong></li>
  <li>The statement must not include PIVOT or UNPIVOT operators. <strong>OK</strong></li>
  <li>The statement must not include the INTERSECT or EXCEPT operators. <strong>OK</strong></li>
  <li>The statement must not reference a view. <strong>OK</strong></li>
  <li>The statement must not contain any of the following: DISTINCT,
  COMPUTE or COMPUTE BY, or INTO. <strong>OK</strong></li>
  <li>The statement must not reference server global variables
  (@@variable_name). <strong>OK</strong></li>
  <li>The statement must not reference derived tables, temporary tables, or
  table variables. <strong>OK</strong></li>
  <li>The statement must not reference tables or views from other databases
  or servers. <strong>OK</strong></li>
  <li>The statement must not contain subqueries, outer joins, or
  self-joins. <strong>OK</strong></li>
  <li>The statement must not reference the large object types: text, ntext,
  and image. <strong>OK</strong></li>
  <li>The statement must not use the CONTAINS or FREETEXT full-text
  predicates. <strong>OK</strong></li>
  <li>The statement must not use rowset functions, including OPENROWSET and
  OPENQUERY. <strong>OK</strong></li>
  <li>The statement must not use any of the following aggregate functions:
  AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP. <strong>OK</strong></li>
  <li>The statement must not use any nondeterministic functions, including
  ranking and windowing functions. <strong>OK</strong></li>
  <li>The statement must not contain user-defined aggregates. <strong>OK</strong></li>
  <li>The statement must not reference system tables or views, including
  catalog views and dynamic management views. <strong>OK</strong></li>
  <li>The statement must not include FOR BROWSE information. <strong>OK</strong></li>
  <li>The statement must not reference a queue. <strong>OK</strong></li>
  <li>The statement must not contain conditional statements that cannot
  change and cannot return results (for example, WHERE 1=0). <strong>OK</strong></li>
  </ul>
  
  <p>But that still doesn't works ... =(</p>
  
  <p><strong>Final edit - Solution :</strong> As Jon Tirjan said, it was caused by my computed column <code>NomComplet</code> which is not valid with the Service Broker (even when I don't ask to be notified on changes on this column, which is strange to me).</p>