SQL Server Notifications - My OnChange does not fire

Question

I would like to make use of SQL Server notifications to capture insert events at my database within a winforms app. I am attempting to use the SQLDependency object. The MSDN articles make this seem pretty straight forward. So I have created a little example application to give it a try. The event only seems to fire as I enter my application the first time(MessageBox appears). Inserting data into the table does not raise the OnChange event it would seem. Can someone tell me what I'm missing? Thanks!

 public Main()
    {
        InitializeComponent();
        var check = EnoughPermission();
        SqlDependency.Stop(constr);
        SqlDependency.Start(constr);
        if(connection == null)
        {
            connection = new SqlConnection(constr);
        }
        if(command == null)
        {
            command = new SqlCommand("Select ID, ChatMessage FROM dbo.Chat",connection);
        }
        connection.Open();
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
        command.ExecuteReader();
    }





    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        MessageBox.Show("Change!");
    }

Where are you inserting data? A SELECT statement isn't an INSERT. — Mark Byers, Dec 15 '09 at 00:24
I am inserting the data in a Query analyzer.. Good call on the INSERT not being a SELECT :) The Select is needed to signify to the database which data you are watching for notifications. — Nick, Dec 15 '09 at 00:27
Notice that I am handing that SELECT to the SqlDendency obj.. — Nick, Dec 15 '09 at 00:28

score 11 · Answer 1 · answered Oct 13 '11 at 15:51

11

While I was working in the implementation of query notification, I got the exact problem. I checked all configurations, code pieces, and even TCP settings, but nothing helped. Then, I figured out the following query to run on database and it solved my problem. Maybe you can try it.

ALTER AUTHORIZATION ON DATABASE::[Your DB] TO sa;

answered Oct 13 '11 at 15:51

nanotech

111
1
3

4

Worked for me also. But I would want to understand what has been done by this query. – bussa Apr 20 '15 at 11:11
`The proposed new database owner is already a user or aliased in the database.` – JP Hellemons May 15 '17 at 10:27

score 6 · Answer 2 · answered Dec 15 '09 at 00:49

Your first notification is the only notification you'll get. Query Notifications are not a subscription for changes, once a notification is fired it is also invalidate. You are supposed to re-submit a new notification subscription.

If your query is notified immedeatly it means you did not get a notification for a change, but one for an invalid query. Check the values of the SqlNotificationEventArgs argument you receive. Check the Info to be Insert/Update/Delete, check the Source to be Data, check the Type to be Change.

Have a look at the Watcher Application example to better understand how you are supposed to re-subscribe when notified. For a better understanding of how the Query Notifications work, see The Mysterious Notification.

So my Select is invalid? The info property has 'invalid'. Thanks Ill take a look at these links you sent! — Nick, Dec 15 '09 at 00:54
The SELECt per se looks valid to me, but there are about a mirriad more criteria. See http://msdn.microsoft.com/en-us/library/ms181122(SQL.90).aspx — Remus Rusanu, Dec 15 '09 at 01:01

SQL Server Notifications - My OnChange does not fire

2 Answers2

Linked