c# - SqlDependency fires immediately -

i want use sqldependency notifications when datas changed others applications using database.

public class databasechangesnotification : idisposable {     private static string chainedeconnexion = configurationmanager.connectionstrings["transfertcontext"].connectionstring;      private static readonly lazy<databasechangesnotification> _instance = new lazy<databasechangesnotification>(() => 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     {                  {             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) => {                     var dependency = sender sqldependency;                     dependency.onchange -= delegateautoremove;                     eventhandler(sender, e);                  };                  sqldependency.onchange += delegateautoremove;                  command.executenonquery();             }         }     } } 

so, single line can register event handler :

databasechangesnotification.instance.abonnernotification(@"select idutilisateur, code, nom, prenom, nomcomplet, login, synchroniser 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<transfertclienthub>().clients.all.hello("users modified !");         //abonnementchanges();     } 

but problem notification immediatly fired :

--- abonnement --- ------------------------------ updatteeee ------------------------- info:   query source: statement type:   subscribe 

that's why commented abonnementchanges in event handler onchanges (or loop infinitely).

i don't know problem comes because reset notifications ({ notification = null }) , request respect requirements (https://msdn.microsoft.com/en-us/library/ms181122.aspx).

edit : want add select * sys.dm_qn_subscriptions returns nothing.

edit : looks comes database configuration, , not implemention, tried implemention result in same behaviour : http://www.codeproject.com/articles/144344/query-notification-using-sqldependency-and-sqlcach

edit : don't see comes since use sa sysadmin , have rights, isn't ?

edit : tried define connection database following tutorial : http://www.codeproject.com/articles/12862/minimum-database-permissions-required-for-sqldepen

so created 2 roles :

exec sp_addrole 'sql_dependency_subscriber'  exec sp_addrole 'sql_dependency_starter'   -- permissions needed [sql_dependency_starter] grant create procedure [sql_dependency_starter]  grant create queue [sql_dependency_starter] grant create service [sql_dependency_starter] grant references on  contract::[http://schemas.microsoft.com/sql/notifications/postquerynotification]   [sql_dependency_starter]  grant view definition [sql_dependency_starter]   -- permissions needed [sql_dependency_subscriber]  grant select [sql_dependency_subscriber]  grant subscribe query notifications [sql_dependency_subscriber]  grant receive on querynotificationerrorsqueue [sql_dependency_subscriber]  grant references on  contract::[http://schemas.microsoft.com/sql/notifications/postquerynotification]   [sql_dependency_subscriber]  

and added user (production) roles :

-- making sure users member of correct role.

exec sp_addrolemember 'sql_dependency_starter', 'production' exec sp_addrolemember 'sql_dependency_subscriber', 'production' 

but connection have same behaviour before. notification fired imediatly :

------------------------------ updatteeee ------------------------- info:   query source: statement type:   subscribe 

edit : tried simpler requests : select nom, prenom dbo.utilisateur. here details of table should inspected :

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]  (([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 

as can see there columns can't requested. that's why don't use it.

now let's check select nom, prenom dbo.utilisateur :

• the projected columns in select statement must explicitly stated, , table names must qualified two-part names. notice means tables referenced in statement must in same database. ok
• the statement may not use asterisk () or table_name. syntax specify columns. ok
• the statement may not use unnamed columns or duplicate column names. ok
• the statement must reference base table. ok
• the projected columns in select statement may not contain aggregate expressions unless statement uses group expression. when group expression provided, select list may contain aggregate functions count_big() or sum(). however, sum() may not specified nullable column. ok
• the statement may not specify having, cube, or rollup. projected column in select statement used simple expression must not appear more once. ok
• the statement must not include pivot or unpivot operators. ok
• the statement must not include intersect or except operators. ok
• the statement must not reference view. ok
• the statement must not contain of following: distinct, compute or compute by, or into. ok
• the statement must not reference server global variables (@@variable_name). ok
• the statement must not reference derived tables, temporary tables, or table variables. ok
• the statement must not reference tables or views other databases or servers. ok
• the statement must not contain subqueries, outer joins, or self-joins. ok
• the statement must not reference large object types: text, ntext, , image. ok
• the statement must not use contains or freetext full-text predicates. ok
• the statement must not use rowset functions, including openrowset , openquery. ok
• the statement must not use of following aggregate functions: avg, count(*), max, min, stdev, stdevp, var, or varp. ok
• the statement must not use nondeterministic functions, including ranking , windowing functions. ok
• the statement must not contain user-defined aggregates. ok
• the statement must not reference system tables or views, including catalog views , dynamic management views. ok
• the statement must not include browse information. ok
• the statement must not reference queue. ok
• the statement must not contain conditional statements cannot change , cannot return results (for example, 1=0). ok

but still doesn't works ... =(

final edit - solution : jon tirjan said, caused computed column nomcomplet not valid service broker (even when don't ask notified on changes on column, strange me).

service broker doesn't work on tables computed columns. need remove nomcomplet table, or change actual column populated way (trigger, stored procedure, etc.)

the notification being fired because error occurs while setting queue.