none
What could be possible reasons for this behavior? RRS feed

  • Question

  • Hello,

    I created a windows service application that uses sqDependency.

    Here is some part.

    command.CommandType = CommandType.StoredProcedure;
    command.Notification = null;
    con.Open();
    SqlDependency sqlDepEvent = new SqlDependency(command);
    sqlDepEvent.OnChange += new OnChangeEventHandler(sqlDepEvent_OnChange);

    void sqlDepEvent_OnChange(object sender, SqlNotificationEventArgs e)
    {
                SqlDependency dependency = sender as SqlDependency;
                if (dependency != null)
                {
                    dependency.OnChange -= sqlDepEvent_OnChange;
                }
                //....

             // Resubmitting OnChange event to register the event.

    }

    Most of time it works fine. OnChange event is invoked, so I can do some business logic here.

    However, RARELY notification seems not firing...as far as I can see in our error log, I don't see any error indication.

    SQL server wasn't stopped and still running. Restarting my service is a way to resolve this notification issue...Once the service restarted, then everything works fine again. ...

    As far as I can think of, I can't spot any code that could cause this kind of issue..hum....

    This may be really dumb question, but what might be possible reason you guys can think of why I am seething this behavior rarely?

    1. If database was somehow stopped and not working at the moment the notification wasn't fired, I could assume/guess that this may be somehow sql server connection issue. However, like I said, this wasn't case as far as I can tell.

    2. Could it be somehow deadlock issue something??..(I am just guessing..maybe concurrent data is being inserted and the event is somehow messed up?????..don't know..maybe??) is this possible reason that cause this kind of issue?

    ....Any other things I may need to look at???

    Well..once a while, I could restart the service..however, I don't think this is good solution.

    Any idea?



    • Edited by T J Thursday, December 13, 2012 3:07 PM
    Thursday, December 13, 2012 3:03 PM

All replies

  • AFIK, SqlDependency notifications are Asynchronous (Queue) and safe from Server Down/Deadlock.

    What .net framework you are using? If it is .net 2.0 there is a known issue and fix available by MS from here.

    You can fire this query on your SQL server database and see transmission_status column to find out if any failure happened.

    select * from sys.transmission_queue


    Lingaraj Mishra

    Monday, December 17, 2012 11:54 AM
  • Thanks for your answer.

    4.0 is the version I am currently using..Then I believe the issue you specified already has been fixed in this version, which means I don't need to apply the fix. Right?

    BTW, out of curiosity,

    "The problem occurs when there is a delay of more than five minutes between the time when the subscription is requested and the time when the notification is fired on the database server. " ..

    Can you elaborate this somehow with more concrete example to understand easier if you can?

    Thanks again.

    Monday, December 17, 2012 2:55 PM
  • Query notifications are supported for SELECT statements that meet the following requirements:

    • The statement must reference a base table.
    • The statement must not reference system tables or views, including catalog views and dynamic management views.

    Can you please double check if your query utilizes any of the above?

    Also, please compare your code with this latest step-by-step post by our MVP:

    http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach

    Tuesday, December 18, 2012 10:56 PM
  • Here is my snippet of code.

    public interface INotify {

        ...

        void Start();

        ...

    }

    public class MySQL : INotify {

       public void Start() {

            ....

            command.CommandType = CommandType.StoredProcedure;
            command.Notification = null;
            con.Open();
            SqlDependency sqlDepEvent = new SqlDependency(command);
            sqlDepEvent.OnChange += new OnChangeEventHandler(sqlDepEvent_OnChange);

            ....

            ...

       }


       void sqlDepEvent_OnChange(object sender, SqlNotificationEventArgs e)
       {
                SqlDependency dependency = sender as SqlDependency;
                if (dependency != null)
                {
                    dependency.OnChange -= sqlDepEvent_OnChange;
                }
                INotify notify  = this as INotify;
                if(notify  != null) {
                    notify.Start();
                }
       }

    }

    Only possible reason of my issue I could think of..

      INotify notify  = this as INotify;
      if(notify  != null) {
             notify.Start();
      }

    I am guessing this part code rarely didn't run ....then one possible reason could be that notify is evaluated as null.

    Maybe threading issue?..well....however, the notify is local variable, so as far as I can think of, it should not be matter in threading..

    Hum.....Any other reason you guys can think of?

    Thanks,


    Friday, December 21, 2012 10:17 PM
  • This can be n-number of things. Your code looks OK to me. Your question requires a more in-depth level of support requireing a local repro. Please visit the below link to see the various support options that are available to assist you further. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
    Monday, December 31, 2012 11:43 PM