I am trying to use SQLDependency in my windows application and have followed the steps defined How can I notify my program when the database has been updated? and http://dotnet.dzone.com/articles/c-sqldependency-monitoring
I have enabled Service Broker, set up the queue, created a servie on queue:
ALTER DATABASE [Company] SET ENABLE_BROKER;
CREATE QUEUE ContactChangeMessages;
CREATE SERVICE ContactChangeNotifications
ON QUEUE ContactChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
The next step is to let the SQL user subscribe to the query notifications. I understand I can provide the login user, which is sa (verified using the query SELECT * FROM sys.server_principals):
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa;
But I am getting "Cannot find the user 'sa', because it does not exist or you do not have permission."
I have used other users like sysadmin too to grant the permission but every time I was getting the same error. Then I read @ http://ask.sqlservercentral.com/questions/7803/msg-15151-level-16-state-1-line-1-cannot-find-the.html) that the permission needs to be provided to a user and not to a login which I did. So now I have provided the permission to 'public' and 'guest' and the sql query executes successfully and not to dbo ("Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.")
The application code in c# is not too complicated and I have followed the links provided at the beginning so not putting the code here (surely I changed the queue name etc. in line with the sql commands above). But the SQlDependency does not seem to be working when I change the table records (insert/delete).
Where am I going wrong? Is there any step which I am missing
