Hello,

I am trying to use the SqlDependency object to maintain a current dataset on
my client code. However, whenever I try to implement it, the event fires
with the following Arguments in the SqlNotificationEventArgs object:

e.Info = Invalid
e.Source = Statement
e.Type = Subscribe

The command I am creating the SqlDependency on implements the following
stored procedure:

ALTER PROCEDURE WorkItemsGet
AS

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
SET ARITHABORT ON

SELECT
JobID,
MachineName
FROM
dbo.T_WorkItems

The Code I have written is as follows:

private SqlConnection connection;
private SqlCommand command;
private SqlDependency dependency;
private DataSet dataToWatch;

private void Form1_Load(object sender, EventArgs e)
{


dataToWatch = new DataSet();

connection = new SqlConnection(GetConnectionString());
connection.Open();
command = new SqlCommand(); //GetSQL(), connection);
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "WorkItemsGet";

dependency = new SqlDependency(command);
bool success = SqlDependency.Start(GetConnectionString());
dependency.OnChange += new
OnChangeEventHandler(dependency_OnChange);

SqlDataAdapter adapter = new SqlDataAdapter(command);

adapter.Fill(dataToWatch);
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = dataToWatch.Tables[0];
}


void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
try
{
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= dependency_OnChange;

command.Notification = null;

dependency = new SqlDependency(command);
dependency.OnChange += new
OnChangeEventHandler(dependency_OnChange);

dataToWatch.Clear();

SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dataToWatch);
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = dataToWatch.Tables[0];
}

Does anyone have any idea what is wrong with my setup?

Thanks.

I dont know off the top of my head if you used nay restricitons in your
stored procedure, but some settings will invalidate the querysubscription
like SET NOCOUNT ON and other ones mentioned here:
http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx

Perhaps you should try to get rid of some of the settings one by one, to see
if they are making your subscriptions to be invalid.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---


"Netanel Livni" <NetanelLivni (AT) discussions (DOT) microsoft.com> wrote