Simple Query Notification - Doesn't Work!!

25 Jun 2006 9:16 PM

×“×•×“

Hi,

I'll try to make it short, thing is - I'm trying to implement a query
notification (Sql Server 2005) in a C# Windows Application. I've been through
plenty samples and guides, I did everything and it still doesn't work.

The Service Broker is enabled (for the specific DB), the syntax is correct
for NS (not using *), the permissions should be fine (tried several different
ways, including the 'sa' account), and I've started SqlDependency
(SqlDependency.Start(connection)).

Also, using the same server and connection string, this works fine on the
tempdb with a Microsoft sample.

In short, all I'm trying to do to beginwith is show a Message when something
changes in the table.

Am I missing something? (I've been on this forever now...)

Thank you for your time.

M. David.

26 Jun 2006 12:30 AM

David Browne

Show quote

"×“×•×“" <@discussions.microsoft.com> wrote in message
news:ED5C2655-E035-4111-829B-C991972D5BEB@microsoft.com...
> Hi,
>
> I'll try to make it short, thing is - I'm trying to implement a query
> notification (Sql Server 2005) in a C# Windows Application. I've been
> through
> plenty samples and guides, I did everything and it still doesn't work.
>
> The Service Broker is enabled (for the specific DB), the syntax is correct
> for NS (not using *), the permissions should be fine (tried several
> different
> ways, including the 'sa' account), and I've started SqlDependency
> (SqlDependency.Start(connection)).
>
> Also, using the same server and connection string, this works fine on the
> tempdb with a Microsoft sample.
>
> In short, all I'm trying to do to beginwith is show a Message when
> something
> changes in the table.
>
> Am I missing something? (I've been on this forever now...)
>

Well, if the sample works, then you're probably missing something. Perhaps
try modifying the sample to look more like your program and see if it stops
working at some point. You will either end up with a fix, or a short
non-working sample that you believe should work.

If you end up with a short, non-working sample that you believe should work
(aka a "repro"), post it here and someone might take a look at it.

David

26 Jun 2006 4:21 PM

×“×•×“

Ok, this is pretty much it:

static SqlDependency MainLogDependency;     // Notification SqlDependency.

        private void MainLogSqlDependencyChange(object caller,
SqlNotificationEventArgs e)
        {
            MessageBox.Show("notify");
        }

and within the _Load method of the form there's this:

            #region SQL Dependency Setup
            //Setup SQL Notification for any change
            string dependencyCommand = "SELECT DateTime, EventType, Frame,
Location, Title, Data, Source, SourceName, SourceDisplay, Recepient, Status
FROM dbo.MainLog";
            SqlCommand cmd = new SqlCommand();

            // unregister any previous eventhandlers
            if (MainLogDependency != null)
                MainLogDependency.OnChange -= new
OnChangeEventHandler(MainLogSqlDependencyChange);

            // Clear out any existing notification information from the
command.
            cmd.Notification = null;

            // Create the SqlDependency and associate it with the query
command.
            cmd.Connection = SQLAgent.LogDBconn; //using a the application's
pulic SQL connection object
            cmd.CommandText = dependencyCommand;
            MainLogDependency = new SqlDependency(cmd);

SqlDependency.Start(ConfigurationManager.ConnectionStrings["ReportsLog.Properties.Settings.ReportsLogDBConnectionString"].ToString());
            MainLogDependency.OnChange += new
OnChangeEventHandler(MainLogSqlDependencyChange);
            #endregion

I hope it's clear enough. I think it should work.

Thanks.

Show quote

"David Browne" wrote:

>
> "×“×•×“" <@discussions.microsoft.com> wrote in message
> news:ED5C2655-E035-4111-829B-C991972D5BEB@microsoft.com...
> > Hi,
> >
> > I'll try to make it short, thing is - I'm trying to implement a query
> > notification (Sql Server 2005) in a C# Windows Application. I've been
> > through
> > plenty samples and guides, I did everything and it still doesn't work.
> >
> > The Service Broker is enabled (for the specific DB), the syntax is correct
> > for NS (not using *), the permissions should be fine (tried several
> > different
> > ways, including the 'sa' account), and I've started SqlDependency
> > (SqlDependency.Start(connection)).
> >
> > Also, using the same server and connection string, this works fine on the
> > tempdb with a Microsoft sample.
> >
> > In short, all I'm trying to do to beginwith is show a Message when
> > something
> > changes in the table.
> >
> > Am I missing something? (I've been on this forever now...)
> >
>
> Well, if the sample works, then you're probably missing something. Perhaps
> try modifying the sample to look more like your program and see if it stops
> working at some point. You will either end up with a fix, or a short
> non-working sample that you believe should work.
>
> If you end up with a short, non-working sample that you believe should work
> (aka a "repro"), post it here and someone might take a look at it.
>
> David
>

27 Jun 2006 10:56 PM

David Browne

"×“×•×“" <@discussions.microsoft.com> wrote in message
news:36836CB3-E82A-4672-9603-0B0854C51C23@microsoft.com...
> Ok, this is pretty much it:
>
> static SqlDependency MainLogDependency; // Notification SqlDependency.
>
.. . .

I think you were just setting up the SqlDependency in the wrong order.

The following worked for me:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SQLDependencyTest
{
public partial class Form1 : Form
{
    string connect =
        "Data Source=(local);Integrated Security=true;Initial Catalog=test";
    SqlConnection con = null;
    SqlDependency MainLogChangeListener = null;

    public Form1()
    {
      InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      con = new SqlConnection(connect);
      con.Open();
      this.Disposed += delegate(object obj, EventArgs args)
      {
        con.Close();
      };

    }

    private void btnCreateDependency_Click(object sender, EventArgs e)
    {
      SqlCommand cmd = new SqlCommand(
          "select DateTime, EventType from dbo.MainLog order by DateTime",
con);

      SqlDependency.Start(connect);
      MainLogChangeListener = new SqlDependency();

      MainLogChangeListener.OnChange +=
          new OnChangeEventHandler(MainLogChangeListener_OnChange);

      MainLogChangeListener.AddCommandDependency(cmd);

      string ev = "";
      using (SqlDataReader rdr = cmd.ExecuteReader())
      {
        while (rdr.Read())
        {
          ev = rdr.GetString(1);

        }
      }

      MessageBox.Show("Last EventType " + ev);

    }

    void MainLogChangeListener_OnChange(object sender,
SqlNotificationEventArgs e)
    {
      MessageBox.Show(string.Format("{0} {1} {2}", e.Source, e.Type,
e.Info));
    }

    private void btnTriggerNotification_Click(object sender, EventArgs e)
    {
      SqlCommand cmd = new SqlCommand(
          "insert into dbo.MainLog(EventType) values ('aa')", con);
      cmd.ExecuteNonQuery();
    }
}
}

David

28 Jun 2006 3:29 PM

David

Well, I changed my code to be the same as yours and still nothing happens.

I'll try to give it a go on a different env/servers and see what happens.

But if you or anyone else have any ideas, I'd still very much like to read
them.

Thanks for all your help.

Show quote

"David Browne" wrote:

>
> "×“×•×“" <@discussions.microsoft.com> wrote in message
> news:36836CB3-E82A-4672-9603-0B0854C51C23@microsoft.com...
> > Ok, this is pretty much it:
> >
> > static SqlDependency MainLogDependency;     // Notification SqlDependency.
> >
> .. . .
>
> I think you were just setting up the SqlDependency in the wrong order.
>
> The following worked for me:
>
>
>
> using System;
> using System.Collections.Generic;
> using System.ComponentModel;
> using System.Data;
> using System.Drawing;
> using System.Text;
> using System.Windows.Forms;
> using System.Data.SqlClient;
>
> namespace SQLDependencyTest
> {
>   public partial class Form1 : Form
>   {
>     string connect =
>         "Data Source=(local);Integrated Security=true;Initial Catalog=test";
>     SqlConnection con = null;
>     SqlDependency MainLogChangeListener = null;
>
>     public Form1()
>     {
>       InitializeComponent();
>     }
>
>
>
>
>     private void Form1_Load(object sender, EventArgs e)
>     {
>       con = new SqlConnection(connect);
>       con.Open();
>       this.Disposed += delegate(object obj, EventArgs args)
>       {
>         con.Close();
>       };
>
>
>     }
>
>
>     private void btnCreateDependency_Click(object sender, EventArgs e)
>     {
>       SqlCommand cmd = new SqlCommand(
>           "select DateTime, EventType from dbo.MainLog order by DateTime",
> con);
>
>       SqlDependency.Start(connect);
>       MainLogChangeListener = new SqlDependency();
>
>
>       MainLogChangeListener.OnChange +=
>           new OnChangeEventHandler(MainLogChangeListener_OnChange);
>
>       MainLogChangeListener.AddCommandDependency(cmd);
>
>
>       string ev = "";
>       using (SqlDataReader rdr = cmd.ExecuteReader())
>       {
>         while (rdr.Read())
>         {
>           ev = rdr.GetString(1);
>
>         }
>       }
>
>       MessageBox.Show("Last EventType " + ev);
>
>     }
>
>     void MainLogChangeListener_OnChange(object sender,
> SqlNotificationEventArgs e)
>     {
>       MessageBox.Show(string.Format("{0} {1} {2}", e.Source, e.Type,
> e.Info));
>     }
>
>     private void btnTriggerNotification_Click(object sender, EventArgs e)
>     {
>       SqlCommand cmd = new SqlCommand(
>           "insert into dbo.MainLog(EventType) values ('aa')", con);
>       cmd.ExecuteNonQuery();
>     }
>   }
> }
>
>
> David
>
>

29 Jun 2006 4:14 PM

Jason Saidoo

Check the owner of the database. I had an issue where notifcations were not
happening when I was disconnected from my work network. The database was
owned by "domain\jsaidoo" and would not work (for some unknown reason). I
switched the database owner to "sa" and it worked flawlessly.

Best Regards,

Jason

Show quote

"×“×•×“" wrote:

> Hi,
>
> I'll try to make it short, thing is - I'm trying to implement a query
> notification (Sql Server 2005) in a C# Windows Application. I've been through
> plenty samples and guides, I did everything and it still doesn't work.
>
> The Service Broker is enabled (for the specific DB), the syntax is correct
> for NS (not using *), the permissions should be fine (tried several different
> ways, including the 'sa' account), and I've started SqlDependency
> (SqlDependency.Start(connection)).
>
> Also, using the same server and connection string, this works fine on the
> tempdb with a Microsoft sample.
>
> In short, all I'm trying to do to beginwith is show a Message when something
> changes in the table.
>
> Am I missing something? (I've been on this forever now...)
>
>
> Thank you for your time.
>
> M. David.

29 Jun 2006 7:35 PM

David

Thanks for your advice, but the Owner is the sa already. so no dice...

Anything else?

M. David.

Show quote

"Jason Saidoo" wrote:

> Check the owner of the database. I had an issue where notifcations were not
> happening when I was disconnected from my work network. The database was
> owned by "domain\jsaidoo" and would not work (for some unknown reason). I
> switched the database owner to "sa" and it worked flawlessly.
>
> Best Regards,
>
> Jason
>
> "×“×•×“" wrote:
>
> > Hi,
> >
> > I'll try to make it short, thing is - I'm trying to implement a query
> > notification (Sql Server 2005) in a C# Windows Application. I've been through
> > plenty samples and guides, I did everything and it still doesn't work.
> >
> > The Service Broker is enabled (for the specific DB), the syntax is correct
> > for NS (not using *), the permissions should be fine (tried several different
> > ways, including the 'sa' account), and I've started SqlDependency
> > (SqlDependency.Start(connection)).
> >
> > Also, using the same server and connection string, this works fine on the
> > tempdb with a Microsoft sample.
> >
> > In short, all I'm trying to do to beginwith is show a Message when something
> > changes in the table.
> >
> > Am I missing something? (I've been on this forever now...)
> >
> >
> > Thank you for your time.
> >
> > M. David.

7 Jul 2006 5:57 AM

Ritesh Sharma

Hi David,
I have just started working on Query Notification services & its
not working out for me.I have followed your thread & was just wondering
if you managed to get it to work in the end?

Kindly let me know as I am desperate.

Thanks,
Ritesh

*** Sent via Developersdex http://www.developersdex.com ***

7 Jul 2006 10:08 AM

David

Actually No. I even took it to a different enviroment (different servers) and
it still doesn't work. I'm still looking myself...

Good Luck.

Show quote

"Ritesh Sharma" wrote:

>
>
> Hi David,
> I have just started working on Query Notification services & its
> not working out for me.I have followed your thread & was just wondering
> if you managed to get it to work in the end?
>
> Kindly let me know as I am desperate.
>
> Thanks,
> Ritesh
>
> *** Sent via Developersdex http://www.developersdex.com ***
>