CODEWITHSUNDEEP
c#sqlsql-server-2012sqldependency
ff8mania
ff8mania

Reputation: 1770

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

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!

Upvotes: 1

Views: 1426

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294307

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

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

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

Upvotes: 1

Related Questions