LINQ to SQL and SqlDependency

Question

Are there any consequences of using SqlDependency and LINQ to SQL together. Or should we take care of some things specifically to make them work correctly?

In our application, we use LINQ to SQL as ORM, and business logic in stored procedures. We cache the SP output and create the SQLDependency. Whenever the output of the SPs changes, the cache becomes invalid.

Our code has so far worked perfectly in Dev, Test, and QA, but recently it has stopped working in QA, and the logs show that the error comes from SqlDependency.Start (ConnStr), which is executed in the global.asax file in the Start block application .

The error is this: Message. If you use SqlDependency without providing a parameter value, you must call SqlDependency.Start () before executing the command added to the SqlDependency instance.

But I do not understand that when SqlDependency.Start () is already running in Application Start, why does it throw an exception after the SP is executed.

Can we shed light on this problem?

Answer 1

Since SQLDependency depends on permissions - have permissions changed on the server?

(They probably shouldn't - but they can!)

Answer 2

, , , , .

-- Permissions  
GRANT CREATE PROCEDURE to [testUser] 
GRANT CREATE QUEUE to [testUser] 
GRANT CREATE SERVICE to [testUser] 
----

GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to  [testUser]

GRANT RECEIVE ON QueryNotificationErrorsQueue TO  [testUser]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [testUser]


GRANT CONTROL ON SCHEMA::[dbo] TO [testUser]
GRANT IMPERSONATE ON USER::DBO TO [testUser]