We've had this issue for a while but I wanted to through it out to the community before we open a ticket with Tessitura. I searched the forum for something similar but didn't find anything.
We get the following error message many many times throughout the day:
The query notification dialog on conversation handle '{18FC4B81-1D01-E811-80CD-0050569547F9}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="">schemas.microsoft.com/.../Code><Description>Cannot find the remote service 'SqlQueryNotificationService-981ae051-0e04-4176-bd18-8f25607c88ed' because it does not exist.</Description></Error>'.
We are on v14, as of last Monday, but prior to that, we were on v12.5 from May 2016 on. We've always had this come up - no matter the version. In addition to the above, I see this happen often:
During the last time interval xxx query notification errors were suppressed.
We last rebooted the SQL Server on 1/19/2018 and so far, we've registered that second message 3 times. The numbers have been 1, 802 and 1642.
My research points to conversations not being closed properly, which makes me believe this is Service Broker related. The message about 'during the last time interval' points to so many errors occurring that they weren't all registered individually by SQL. Having 1642 of these suppressed once sounds like a bad thing.
Has anyone seen anything like this in their environment? I've come across solutions on the web but I'm hesitant to implement anything because I don't know the full architecture underneath the messaging. If you have a solution that's worked, please share. Thanks!
I'm wondering if this is due to the impresario_cci database not having an encrypted master key. If you run SELECT name, is_master_key_encrypted_by_server FROM sys.databases you'll see that the impresario database is master key encrypted while impresario_cci is not. My thoughts are that since service broker dialog security is what allows the application to use authentication, authorization, and encryption and all dialog conversations use dialog security by default, this could be causing the errors.