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