none
Query Notification not working - Attention event in SQL Profiler RRS feed

  • Question


  • I have the following code to get notification when the tblTest table has new data added to it:

            private void button1_Click(object sender, EventArgs e)
    
    
    
            {
    
    
    
                // Remove any existing dependency connection, then create a new one.
    
    
    
                string connectionString = @"Data Source=PIXEL-IHANNAH2\PPINSTANCE;User ID=sqlPPUser;Password=ppuser1;Initial Catalog=Ian;";
    
    
    
                SqlDependency.Stop(connectionString);
    
    
    
    
    
    
    
                SqlConnection sqlConnection = new SqlConnection(connectionString);
    
    
    
    
    
    
    
                string statement = "select test from dbo.tblTest";
    
    
    
                SqlCommand sqlCommand = new SqlCommand(statement, sqlConnection);
    
    
    
    
    
    
    
                // Create and bind the SqlDependency object to the command object.
    
    
    
                SqlDependency dependency = new SqlDependency(sqlCommand, null, 0);
    
    
    
                SqlDependency.Start(connectionString);
    
    
    
    
    
    
    
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
    
    
    
            }
    
    
    
            
    
    
    
            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    
    
    
            {
    
    
    
            }

    I believe that have enabled everything in my SQL Server 2005 database to allow this to work but I am not getting any notifications.

    When I add new data to the tblTest table (in SQL Management studio), SQL Profiler looks as if it is trying to select this new row added (as I can see a select call on this table) but then I get an Attention event (which is in red) - but there is no information on what has gone wrong. I assume that this event means that it will not work.

    Does anyone have any ideas about how I can work out why it is not working? I have tried to run this sample test on two separate machines and it does not work on either of them. For information I am using Visual Studio 2008 and SQL Server 2005 Version 9.00.4035.00.

    Thanks
    Ian
    • Edited by Ian Hannah Tuesday, February 2, 2010 10:50 AM
    Monday, February 1, 2010 4:26 PM

Answers

All replies

  • Hello Ian,

     

    Welcome to ADO.NET Data Providers forum!

     

    First please verify that all the settings of Notification Services are enabled on the SQL Server 2005 (SQL Server Express does not have such feature).  For detail, please see Enabling Query Notifications (ADO.NET).  Besides, could you please provide us with more detailed information about the Attention event in the SQL Server Profiler that you mentioned?   I think it is very important to troubleshoot the cause of this problem.   

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 2, 2010 8:12 AM
    Moderator
  • One thing could be that you are associating the SqlDependency with a different command than you are creating earlier in the button click.
    I.e "command" rather than "sqlcommand".

    //Michael
    This posting is provided "AS IS" with no warranties.
    Tuesday, February 2, 2010 8:17 AM
  • Michael,

    I have corrected the above code (and I have corrected it in the post) and it still does not work.

    Another user pointed out that I should not use select * so my statement is now "select test from tblTest" and I am still not getting notifications.

    Thanks
    Ian

    Tuesday, February 2, 2010 10:50 AM
  • Lingzhi,

    I have run the following commands on my database as you suggested:

    CREATE QUEUE ContactChangeMessages;

    CREATE SERVICE ContactChangeNotifications
      ON QUEUE ContactChangeMessages
    ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

    It was also suggested to me that I should specify the column names in the select statement (which I have now done). I have run the code (with SQL Profiler) and I cannot see any errors but I am still not getting any notification. Any ideas what how I can diagnose what the problem is?

    Thanks
    Ian

    Tuesday, February 2, 2010 11:15 AM
  • Hi again Ian

    I'm not an SqlDependency expert as such, but I found this:

    "Using SqlDependency in a Windows Application" 
    http://msdn.microsoft.com/en-us/library/a52dhwx7(VS.80).aspx

    and followed the steps there, for me it worked as expected, hopefully it will for you as well.

    //Michael


    This posting is provided "AS IS" with no warranties.
    Tuesday, February 2, 2010 3:40 PM
  • Michael, Thanks for the link. Essentially it works which is great. The thing that I was not doing was populating a dataset in memory. This is the only real difference. It seems as if the data to compare against must be cached on the client. Anyway it is doing the job. The only thing that I am slightly confused about is that the dependency_OnChange event is called constantly and not just when data changes. Any idea why this may be happening? Thanks Ian
    Tuesday, February 2, 2010 4:54 PM
  • Hi Ian,

     

    I am glad that the SqlDependency works great now.  

     

    For the question OnChange is fired constantly, could you please tell us what is the event info by accessing SqlNotificationEventArgs.Info?   The value should be one of this SqlNotificationInfo enumeration. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 3, 2010 8:56 AM
    Moderator
  • Lingzhi,

    It is all working now - I did not fully qualify the table name i.e. I had:

    SELECT test from tblTest where number > 10

    rather than

    SELECT test from dbo.tblTest where number > 10

    When I did not use dbo the SqlNotificationInfo was invalid.

    Thanks for all your help
    Ian

     

    Wednesday, February 3, 2010 3:43 PM
  • Hi Ian,

     

    Great!  I am glad to hear that the problem is solved.  J  

     

    Also, thanks to Michael, for your contribution. 

     

    Have a great day, you both!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, February 4, 2010 9:10 AM
    Moderator