SqlDependency fires immediately

Question

I want to use SqlDependency to get notifications when some datas are changed by others applications using the 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
    {
        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) => {
                    var dependency = sender as SqlDependency;
                    dependency.OnChange -= delegateAutoRemove;
                    eventhandler(sender, e); 
                };

                sqlDependency.OnChange += delegateAutoRemove;

                command.ExecuteNonQuery();
            }
        }
    }
}

So, with a single line i can register an event handler :

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<TransfertClientHub>().Clients.All.hello("users modified !");
        //AbonnementChanges();
    }

But my problem is that the notification is immediatly fired :

--- ABONNEMENT ---
------------------------------ UPDATTEEEE -------------------------
Info:   Query
Source: Statement
Type:   Subscribe

That's why I commented AbonnementChanges in my event handler OnChanges (or it will loop infinitely).

I don't know where the problem comes from because I reset the notifications ({ Notification = null }) and my request respect the requirements (https://msdn.microsoft.com/en-us/library/ms181122.aspx).

Edit : I want to add that select * from sys.dm_qn_subscriptions returns nothing.

Edit : It looks like it comes from database configuration, and not from my implemention, as i tried another implemention which result in the same behaviour : http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach

Edit : I don't see where it comes from since i use SA which is sysadmin and have all rights, isn't it ?

Edit : I tried to define another connection to the database following this tutorial : http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen

So i created 2 roles :

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]

and then i added the user (production) to this roles :

-- Making sure that my users are member of the correct role.

EXEC sp_addrolemember 'sql_dependency_starter', 'production'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'production'

But with this connection i have the same behaviour than before. Notification are fired imediatly :

------------------------------ UPDATTEEEE -------------------------
Info:   Query
Source: Statement
Type:   Subscribe

Edit : I tried with simpler requests like : SELECT Nom, Prenom FROM dbo.Utilisateur. Here are the details of the table which should be 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]  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

As we can see there are some columns which can't be requested. That's why i don't use it.

Now let's check with SELECT Nom, Prenom FROM dbo.Utilisateur :

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. OK
The statement may not use the asterisk () or table_name. syntax to specify columns. OK
The statement may not use unnamed columns or duplicate column names. OK
The statement must reference a base table. OK
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. OK
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. OK
The statement must not include PIVOT or UNPIVOT operators. OK
The statement must not include the INTERSECT or EXCEPT operators. OK
The statement must not reference a view. OK
The statement must not contain any of the 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 from other databases or servers. OK
The statement must not contain subqueries, outer joins, or self-joins. OK
The statement must not reference the large object types: text, ntext, and image. OK
The statement must not use the CONTAINS or FREETEXT full-text predicates. OK
The statement must not use rowset functions, including OPENROWSET and OPENQUERY. OK
The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP. OK
The statement must not use any nondeterministic functions, including ranking and windowing functions. OK
The statement must not contain user-defined aggregates. OK
The statement must not reference system tables or views, including catalog views and dynamic management views. OK
The statement must not include FOR BROWSE information. OK
The statement must not reference a queue. OK
The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0). OK

But that still doesn't works ... =(

Final edit - Solution : As Jon Tirjan said, it was caused by my computed column NomComplet 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).

*I want to add that `select * from sys.dm_qn_subscriptions` returns nothing* Well yeah, `SqlDependency` unsubscribes once it gets a notification. Your event is fired, no more subscription. — ta.speot.is, Apr 02 '15 at 10:26
Please do not [cross post this](http://dba.stackexchange.com/questions/96886/sqldependency-fires-immediately) on DBA.SE next time. — LowlyDBA - John M, Apr 03 '15 at 13:20
Do you make use of stored procedures for your data manipulations? According to http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events, using `SET NOCOUNT ON` in your stored procedure will invalidate it for usage in Query Notifications. — rskar, Apr 03 '15 at 16:05

score 5 · Accepted Answer · answered Apr 03 '15 at 16:26

5

Service Broker doesn't work on tables with computed columns. You need to remove NomComplet from your table, or change it to an actual column which is populated another way (trigger, stored procedure, etc.)

The notification is being fired immediately because an error occurs while setting up the queue.

answered Apr 03 '15 at 16:26

Jon Tirjan

3,556
2
16
24

1

Interesting answer thanks. I will check but I'm not home this weekend. – antoinestv Apr 03 '15 at 18:03
Yes ! It is indeed the problem. Thank you ! – antoinestv Apr 07 '15 at 07:22

score 0 · Answer 2 · edited May 23 '17 at 11:51

Thanks George Stocker for deleting my previous answer, but I had a serious issue with SqlDependency and I insist:

Be careful using SqlDependency class - it has the problems with memory leaks.

For my project I've used open source realization - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Hope this help.

SqlDependency fires immediately

2 Answers2