SqlDependency is not working event if queues are present and query is valid

Question

with the following code, my OnChange event is not raised:

SqlDependency.Start(connectionString);

SqlConnection conn = new SqlConnection(connectionString);
   conn.Open();

                using (SqlCommand command = new SqlCommand(
                    "SELECT Name, Description FROM dbo.Boms",
                    conn))
                {

                    SqlDependency dep = new SqlDependency(command);
                    dep.OnChange += dep_OnChange;

                    // Execute the command.
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        // Process the DataReader.
                    }
                }

I googled for any possible constrains to the query, but my query seems to be ok. The dep object is also properly created, but I tried to insert (successfully) a record inside my Boms table but I received no feedback or event. Inside Sql Server 2012 I see a new queue everytime I launch my application.

I looked for errors into system table but all seems ok.

Any hint to understand at least where is the problem?

Thanks!

@LIUFA: I didn't write that I also use this method that returns me true: private bool CanRequestNotifications() { SqlClientPermission permission = new SqlClientPermission( PermissionState.Unrestricted); try { permission.Demand(); return true; } catch (System.Exception) { return false; } } — ff8mania
– ff8mania, Commented May 28, 2014 at 9:49

Remus Rusanu · Accepted Answer · 2014-06-02 08:38:18Z

1

Read The Mysterious Notification, Troubleshooting Query Notifications Troubleshooting Dialogs and Using SQL Trace to Troubleshoot Query Notifications for troubleshooting tips.

Using profiler, monitor the Query Notifications Event Category to see how QN subscriptions are created and notified.
Using profiler, monitor Broker Event Category to see how the notifications are delivered, specially check if delivery fails.
Using profiler, monitor for Audit Broker Conversation Event Class to see if notification delivery runs into security issues.

Run this to ensure the executes as sandbox context does not run into orphaned dbo sid issues:

ALTER AUTHORIZATION ON database::[<yourdbname>] TO [sa];

answered Jun 2, 2014 at 8:38

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDependency is not working event if queues are present and query is valid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related