SqlDependency timeout problem

.NET Framework Data Access and Storage

rileyt

I'm on XP, SqlSever 2005, .NETfx 3. I'm using SqlDependency to get notifications of changes to my data store. My intent is that when I set up a SqlDependency object, that it will be in effect "forever". However, I find that after 2 minutes or so, changes will not trigger notifications anymore.

I initialize the dependency once per app domain thusly:

SqlDependency.Start(_connectionString);

this._dependencyConnection = new SqlConnection(_connectionString + "Connection Timeout=" + Int32.MaxValue.ToString());

_dependencyConnection.Open();

I set up a new notification (once at startup, and after each notification) like this:

SqlCommand command = new SqlCommand("SELECT Revision FROM dbo.ConfigData where [Key] = '" + _ourKey + "'", _dependencyConnection);

SqlDependency dependency = new SqlDependency(command,null,Int32.MaxValue);

dependency.OnChange += new OnChangeEventHandler(this.OnChange);

SqlDataReader reader = command.ExecuteReader();

reader.Close();

This will fire notifcation for a change that happens within 2 minutes or so. After that, nothing. What am I missing here Thanks

Riley
Top

Re: .NET Framework Data Access and Storage SqlDependency timeout problem

Fox-Jazz

http://msdn2.microsoft.com/en-us/library/ms379594(VS.80).aspx

On the return, you can check to see if itĄŻs a timeout on the notification.

static void MyOnChanged(object caller, SqlNotificationEventArgs e) {

Console.WriteLine("result has changed");

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

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

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

If the source = "timeout" you know it's a timeout and delegate out another dependency statement.


Top

Re: .NET Framework Data Access and Storage SqlDependency timeout problem

rileyt

Thanks for the idea, but no, I'm not getting any messages when the notification times out. It seems to make no difference how I set the timeout on the connection, command, or SqlDependency object - short, long, default. Same deal, silent ending of notifications after 2 minutes or so.

R.


Top

Re: .NET Framework Data Access and Storage SqlDependency timeout problem

Mikhail Karpuk

I have the same problem.

Try this patch:

http://support.microsoft.com/Default.aspx kbid=913364#top

Probably it will help.


Top