Hello,
I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?
Mike
Service Broker is built in into the SQL Server engine, is not possible to install/uninstall the broker compenent independently of the engine itself. Is has no external files/folders whatsowever. So if you have a SQL Server 2005 instanc eup and running, then Service Broker is up and running too. That being said, individual databases can have the broker in them disabled. To check this, look into the is_broker_enabled column in sys.databases. I guess the 'Broker folder' adice you found refers to the Object Explorer view in SQL Server Management Studio.
One place I would look first to troubleshoot SqlDependency issues is the sys.transmission_queue view in your database. If notifications were fired but cannot be delivered, you will find them there, pending delivery. The transmission_status column should indicate the reason why they cannot be delivered.
HTH,
~ Remus
Remus,
Does this apply to SQL Server Express as well?
Thanks for you response,
Michael
|||Yes. Note that the Express version of the Management Studio doesn't have a 'Service Broker' folder in it's Object Explorer.
HTH,
~ Remus
Remus,
There is nothing in the sys.transmission_queue. I'm not even sure messages are GETTING to the database. In my event log, I just noticed a message I haven't seen before. It says:
Service Broker needs to access the master key in the database 'EventSystem'. Error code:25. The master key has to exist and the service master key encryption is required.
I'm not sure what this means. I suppose it's a starting point. Have you seen that one before?
Overall, I'm VERY frustrated with the Service Broker and cache dependencies. I've added more stored procedures and granted more permissions than should be necessary for a feature that is "built in" and "easy to use". So far, the exact OPPOSITE is true.
Thanks for all the help!
Michael
|||I found a post that suggested I needed to create a master key. I ran the following statement:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1ComplexPassword!'
Next, I ran my code again, and saw the following error in the logs:
The query notification dialog on conversation handle '{A1AFE272-1391-DB11-B928-0004230B9AA7}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-15eb7e6d-e2b3-4355-b082-8f0235f5fe3f' because it does not exist.</Description></Error>'.
I thought I had run all procs that were necessary after going through literally a dozen pages on how to set this all up, but perhaps I missed one? I sure wish a single web site had steps that I could follow to get this all to work, start to finish. I promise to post all my steps when I am done, because I am sure others are having the same problem as me.
Michael
|||I had similar problems.
The reason for my problem was that the database was in SQL Server 2000 compatibility mode.
Changing that to SQL Server 2005 helped.
No comments:
Post a Comment