none
Another Query Notification / SqlDependency Question RRS feed

  • Question

  • I seem to be spinning my wheels on getting this to work.  I've tried dozens of variations on the code and the setup and I can't figure out what I'm doing wrong.

    First, in SQL Server 2005 Enterprise Edition, I created a database called Intercom, added the needed tables and test data, and then ran these commands:

    ( MyDomain\MyUser is not the real domain user name (or password).  I’m using an account that we have working for a web application.) 

    USE MSDB

    GRANT SEND ON SERVICE::[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] TO [MyDomain\MyUser];

     

    USE Master

    EXEC sp_configure 'clr enabled' ,1

    RECONFIGURE

    ALTER DATABASE Intercom SET ENABLE_BROKER

     

    use Intercom

    GRANT CREATE PROCEDURE TO [MyDomain\MyUser];

    GRANT CREATE QUEUE TO [MyDomain\MyUser];

    GRANT CREATE SERVICE TO [MyDomain\MyUser];

    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [MyDomain\MyUser];

    GRANT SELECT ON OBJECT::dbo.tbl_message TO [MyDomain\MyUser];

    GRANT RECEIVE ON QueryNotificationErrorsQueue TO [MyDomain\MyUser];

    Create

     

    QUEUE IntercomNotificationQueue

    CREATE SERVICE IntercomService ON QUEUE IntercomNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])

    GO

    Then I typed in the simplest sample program I could find, and used a valid connection string: 

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Data.SqlClient;

    using System.Text;

    using System.Threading;

    using System.Security.Permissions; 

     

    namespace ConsoleApplication1

    {

        class Program

        {       

            static void Main(string[] args)

            {           

                String strConn = "Data Source=TheRealServerNameIsHereInMyApp\\RSN;Initial Catalog=Intercom;Integrated Security=true;User ID=MyDomain\\MyUser;Password=ThePassword";

     

                SqlDependency.Start(strConn);

                using (SqlConnection conn = new SqlConnection(strConn))

                using (SqlCommand cmd = new SqlCommand("SELECT ADUID, MSG_ID, TimeStamp, SentFrom, MSG FROM dbo.tbl_message WHERE (ADUID = SUSER_NAME())", conn))          

     

                {

                    try

                    {

                        SqlDependency depend = new SqlDependency(cmd);

                        depend.OnChange += new OnChangeEventHandler(MyOnChange); 

                        conn.Open();

                        SqlDataReader rdr = cmd.ExecuteReader();

                        while (rdr.Read())

                            Console.WriteLine(rdr[0]);

                        rdr.Close();

                        Console.WriteLine("Press Enter to continue");

                        Console.ReadLine();

                        SqlDependency.Stop(strConn);

                    }

                    catch (Exception e)

                    { Console.WriteLine(e.Message); }

                }

            }

     

            static void MyOnChange(object caller, SqlNotificationEventArgs e)

            {

                Console.WriteLine("result has changed");

                Console.WriteLine("Source " + e.Source);

                Console.WriteLine("Type " + e.Type);

                Console.WriteLine("Info " + e.Info);

     

                //SqlDependency dependency = caller as SqlDependency;

                // Remove the existing Notice so a new event can be added

                //dependency.OnChange -= MyOnChange;

                //Console.WriteLine("Reset ");

     

            } 

        }

    }

     

    When I run the application, the MyOnChange event fires immediately.  I never make any changes to the database itself.  I can make all the changes I want to after that and they are ignored by my console app.

     

    Any help would be appreciated.  I've tried the examples from several books to no avail.  This C# example was the simplest test I could find and came from the fabulous amazing wonderful book *wink wink* called  "A Developers guide to SQL Server 2005" by Bob Beauchemin and Dan Sullivan. 

     

     

     

     

    Thursday, October 14, 2010 10:20 PM

Answers

  • Hi Narnian,

    When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

    ·         ANSI_NULLS ON

    ·         ANSI_PADDING ON

    ·         ANSI_WARNINGS ON

    ·         CONCAT_NULL_YIELDS_NULL ON

    ·         QUOTED_IDENTIFIER ON

    ·         NUMERIC_ROUNDABORT OFF

    • ARITHABORT ON

    The statement must not run under READ_UNCOMMITTED or SNAPSHOT isolation levels. If these options or the isolation level is not set appropriately, the notification is fired immediately after the SELECT statement is executed. Please have a check.

    There are also many restrictions for the supported SELECT statements. If you use unsupported select statement, you will get unwanted consequences. For a list of supported SELECT statements, you can refer to:
    http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx

    I think your problem is within the where clause of your statement using SUSER_NAME(), it may cause the unwanted consequences you encountered. You can try to remove it and try again.

    Best regards,

    Alex Liang

    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.
    • Marked as answer by Narnian Friday, October 15, 2010 5:39 PM
    Friday, October 15, 2010 9:13 AM
    Moderator

All replies

  • Hi Narnian,

    When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

    ·         ANSI_NULLS ON

    ·         ANSI_PADDING ON

    ·         ANSI_WARNINGS ON

    ·         CONCAT_NULL_YIELDS_NULL ON

    ·         QUOTED_IDENTIFIER ON

    ·         NUMERIC_ROUNDABORT OFF

    • ARITHABORT ON

    The statement must not run under READ_UNCOMMITTED or SNAPSHOT isolation levels. If these options or the isolation level is not set appropriately, the notification is fired immediately after the SELECT statement is executed. Please have a check.

    There are also many restrictions for the supported SELECT statements. If you use unsupported select statement, you will get unwanted consequences. For a list of supported SELECT statements, you can refer to:
    http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx

    I think your problem is within the where clause of your statement using SUSER_NAME(), it may cause the unwanted consequences you encountered. You can try to remove it and try again.

    Best regards,

    Alex Liang

    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.
    • Marked as answer by Narnian Friday, October 15, 2010 5:39 PM
    Friday, October 15, 2010 9:13 AM
    Moderator
  • It was the simplest component of your recommendation.  The SUSER_NAME() in the where clause must trigger some type of modification to the table.  As soon as I removed that the test program worked perfectly.  I'll have to change my approach a little - I'll feed the username from active directory instead of asking SQL for it.  The important thing is that the event fires correctly now.  Thanks! 

     

     

    Friday, October 15, 2010 5:43 PM