Simple Query Notification - Doesn't Work!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
דוד said:
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...)
Click to expand...

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
 
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.
 
דוד said:
Ok, this is pretty much it:

static SqlDependency MainLogDependency; // Notification SqlDependency.
Click to expand...
.. . .

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
 
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.
 
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
 
Thanks for your advice, but the Owner is the sa already. so no dice...

Anything else?

M. David.
 
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
 
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.
 
Back
Top