2

I am setting a dependency and then check every minute that the dependency still exists and that HasChanges is NOT true.

I am doing this every minute as I am having trouble getting consistent notifications of changes.

The notification works perfectly the FIRST time there is a change to the database, but even though I reset the dependency it never gets fired again.

NOTE: I have 2 servers and I run practically the same code on each. On the machine where the SQL DB resides I get NO problems. But on the second machine where the SQL connection is to the DB of the first machine - I only get a notification once even though I reset the dependency.

Even if I debug and watch the variable "dependency" in HasDependency function _ I see HasChanges = false EVEN when I have deliberately changed the table.

The SqlDependency query is exactly the same from both servers.

Has anyone had a similar issue?

EDIT1: I found this error in the SQL Server log:

The query notification dialog on conversation handle '{11D06BD6-9CDD-E611-A46F-141877596DFF}.' closed due to the following error: '-8490 Cannot find the remote service 'SqlQueryNotificationService-49a0b301-7c6f-439b-bac5-50ec9dfe6922' because it does not exist.'.

Code:

   public void WakeUpEveryMinute()
            {
                if (Master.G.OUTPUT) Master.G.OUTMGR.WriteLine("[calcSignal] Checking dependency");
            if (!sqlOMS2.HasDependency())
            {
                if (Master.G.OUTPUT) Master.G.OUTMGR.WriteLine("[calcSignal] Setting dependency");

                sqlOMS2.InitialiseDependency(calcSignalForDepedency);
            }
            return;
        }

    public void InitialiseDependencyWORK(Action onDependencyMethod)
    {
        this.onDependencyMethod = onDependencyMethod;
        SqlDependency.Start(connectionString, null);

        using (SqlCommand command = new SqlCommand("SELECT [Symbol] FROM [" + AccountCode + "].[Orders] WHERE [Status] = 'NEW'",conn))
        {
            Dependency = new SqlDependency(command);
            Dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process the DataReader.
            }
        }
    }

    void OnDependencyChange(object sender,   SqlNotificationEventArgs e)
    {
        Console.WriteLine("[OnDependencyChange] " + DateTime.UtcNow.ToString("HH:mm:ss.fff"));
        onDependencyMethod();

    }

    public bool HasDependency()
    {

        if (Dependency == null || Dependency.HasChanges)
            return false;

        return true;
    }
Ziv Weissman
  • 4,400
  • 3
  • 28
  • 61
ManInMoon
  • 6,795
  • 15
  • 70
  • 133
  • Maybe this post is usefull - http://stackoverflow.com/questions/17600537/service-broker-messages-start-to-get-hung-up-after-about-a-day – Ziv Weissman Jan 18 '17 at 21:04
  • Possible duplicate of [SQLDependency\_OnChange-Event fires only one single Time](http://stackoverflow.com/questions/9430896/sqldependency-onchange-event-fires-only-one-single-time) – Cameron Jan 18 '17 at 21:50

0 Answers0