I am using service broker/Sqldependency to monitor table changes. Actual console application runs as windows service on a server different than the sql server. We stop and start the service everyday. Its working fine other than the two errors happening everyday
First error corresponds to the time that we stop and start the windows service which in tern stops and starts the sqldependency
Service Broker needs to access the master key in the database 'XXXX'. Error code:32. The master key has to exist and the service master key encryption is required.
Second error corresponds to the timeframe when the first change happens in the table after stop and start of sqldependency
The query notification dialog on conversation handle '{4443C789-1047-E511-80DA-005056A32BA6}.' closed due to the following error: '-8490Cannot find the remote service 'SqlQueryNotificationService-555c9c9d-d01a-423c-8710-7e9e9a63fbca' because it does not exist.'.5
Following is the code snippet I am using
OnService Start:
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
OnService Stop
SqlDependency.Stop(connectionString);
In notification event
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
I am not sure what else I am missing that's causing the above listed errors. Could someone please help me to understand Whats causing these errors and whats the resolution. The service seems to be running fine other than these two errors everyday.
Appreciate your time and reply