I've implemented SQL Notifications for caching between my SQL Server 2008 database and my ASP.NET web application. I'm seeing this exception in the application even log on our web server, presumably when the application is restarting:
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
I know that the exception occurs in Application_Start because of the stack trace included in the event log entry. I'm at a loss for why this is happening. Here's the code I'm currently using:
The CacheControl PreLoad() call just loads up a bunch of data that I want already in the cache when the application starts, i.e.:
private static object preLoadLock = new object();
/// <summary>
/// Pre-load our data. Only should be called once during application startup.
/// </summary>
public static void PreLoad()
{
lock (preLoadLock)
{
LoadCategoryData();
}
}
if (ds == null)
{
throw new Exception("Unable to load category data into CategoriesSet.");
}
return ds;
}
I've thought about calling SqlDepencency.Stop right before SqlDependency.Start in Application_Start just to be sure that the listener has definitely been stopped, but I don't see why that would be necessary if Application_End was successfully called since
SqlDependency.Stop was called there. The solution I'm resorting to right now is when the problem occurs and the web application is hosed up, I just restart its application pool manually. which sorts the entire issue out until the next time the application
restarts itself. I have the application pool set to default values, so it restarts itself every 1740 minutes.
Has anyone else had a similar problem or any suggestions? I've been searching Google high and low for any information, but haven't found anything other than the usual suggestions of having to call Start in the first place. I'd like to get this issue licked
as soon as possible. Thanks!
Thanks for the links, but I had already read through all of those and basically nothing there offers a solution to what I'm seeing in our application. I'll keep debugging and digging. If anyone out there has a suggestion though, I'd appreciate it.
The first exception occurs when I call my PreLoad method on caching data in Global.asax.cs. The stack trace is below. I see different stack traces on different pages as SqlDependency isn't available when loading data. When I woke up this morning I did
notice 3 different queues for the database in question, which I find kind of odd since I only call Start/Stop on SqlDependency in Global.asax.cs. I would have thought there would only be one queue.
Also, the errors only happened this last time (last night around 2:00am EST) for about a 7-8 minute window. At some point after that window, everything with the SqlDependency started to work again.
An idea I had to curtail this issue for the time being is put a try/catch around loading the data with a dependency. If it fails, load the data without the dependency. At least I'll get my data back, even though it won't come out of the cache. As SqlDependency
becomes available again, caching should resume. The only thing about this is that it doesn't solve whatever issue is at hand and may just mask a potential bigger issue.
I appreciate any help you or anyone else out there might be able to provide for me. Thanks!
-Bill
Exception information:
Exception type: InvalidOperationException
Exception message: When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
Thread information:
Thread ID: 45
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlDependency.GetDefaultComposedOptions(String server, String failoverServer, IdentityUserNamePair identityUser, String database)
at System.Data.SqlClient.SqlCommand.CheckNotificationStateAndAutoEnlist()
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Graydoor.Icarus.ToolBox.CacheControl.LoadCategoryData() in d:\wwwroot\public\frightcatalog\trunk\src\Graydoor.Icarus\ToolBox\CacheControl.cs:line 336
at Graydoor.Icarus.ToolBox.CacheControl.PreLoad() in d:\wwwroot\public\frightcatalog\trunk\src\Graydoor.Icarus\ToolBox\CacheControl.cs:line 32
It just happeend again about 30 minutes ago and I managed to find this in the SQL Server Logs at the same time our site was unavailable:
The query notification dialog on conversation handle '{A1FB449B-DEB3-E011-B6D2-002590198D55}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote
service has been dropped.</Description></Error>'.
After a bunch of those types of messages on different conversation handles, I see:
Query notification delivery could not send message on dialog '{DF974AF3-F0AF-E011-B6D2-002590198D55}.'. Delivery failed for notification '<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="1217214"
type="change" source="timeout" info="none" database_id="5" sid="0x1F59122ACF8727448DA988D041A1D484"><qn:Message>247caab1-7cfa-4374-89ce-a9e6010c5ac7;770480f8-444b-424e-a4c7-7101a1e3963f</qn:Message></qn:QueryNotification>' because of the following error in
service broker: 'The conversation handle "DF974AF3-F0AF-E011-B6D2-002590198D55" is not found.'.
Query notification delivery could not send message on dialog '{DF974AF3-F0AF-E011-B6D2-002590198D55}.'. Query notification delivery failed because of the following error in service broker: 'The conversation handle "DF974AF3-F0AF-E011-B6D2-002590198D55"
is not found.'. See the error log for additional information.
Obviously all of those error messages represent some different handles, but there's a ton of them in the log around this time.
I went back to when it happened earlier in the morning and found the same type of error messages. A quick search online reveals this MSDN support article:
http://support.microsoft.com/kb/958006
We got our version of SQL Serve 2008 no more than 2 months ago, so I would think this cumulative update would be part of our install based on the wording of this article.
I was able to get our site working again by manually shutting down the applicatoin pool for our web application and then starting it back up. At that point everything seems to be fine and query notifications are working properly.
At any given time, even when our application is working fine, I see error messages in the log like the 3rd one listed in bold above. Not sure if that's normal or not.
These type of issues are difficult to troubleshoot in a forum thread as they require a more in-depth level of support. Please visit the below link to see the various paid support options that are available to better meet your needs.
http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
It is hard to tell, but you may need code to clean up the existing query notification subscriptions either when the application starts up or shuts down.
billr578
Member
641 Points
134 Posts
SqlDependency Start Exception
Jul 13, 2011 04:03 AM|LINK
I've implemented SQL Notifications for caching between my SQL Server 2008 database and my ASP.NET web application. I'm seeing this exception in the application even log on our web server, presumably when the application is restarting:
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
I know that the exception occurs in Application_Start because of the stack trace included in the event log entry. I'm at a loss for why this is happening. Here's the code I'm currently using:
protected void Application_Start(Object sender, EventArgs e)
{
SqlDependency.Start(GetConnectionString());
// Pre-load our cacheable data
CacheControl.PreLoad();
}
protected void Application_End(Object sender, EventArgs e)
{
// Stop SqlDependency notifications
SqlDependency.Stop(GetConnectionString());
}
The CacheControl PreLoad() call just loads up a bunch of data that I want already in the cache when the application starts, i.e.:
private static object preLoadLock = new object();
/// <summary>
/// Pre-load our data. Only should be called once during application startup.
/// </summary>
public static void PreLoad()
{
lock (preLoadLock)
{
LoadCategoryData();
}
}
private static DataSet LoadCategoryData()
{
DataSet ds = null;
using (SqlConnection conn = new SqlConnection(Global.GetConnectionString()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT dbo.[Categories].[CategoryID], dbo.[Categories].[ParentCategoryID], dbo.[Categories].[CategoryName], dbo.[Categories].[DisplayOrder] FROM dbo.[Categories]";
SqlCacheDependency scd = new SqlCacheDependency(cmd);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
ds = new DataSet();
sda.Fill(ds);
}
if (ds != null)
{
HttpContext.Current.Cache.Insert("CategoriesSet",
ds,
scd,
DateTime.Now.AddHours(24),
System.Web.Caching.Cache.NoSlidingExpiration);
}
}
}
if (ds == null)
{
throw new Exception("Unable to load category data into CategoriesSet.");
}
return ds;
}
I've thought about calling SqlDepencency.Stop right before SqlDependency.Start in Application_Start just to be sure that the listener has definitely been stopped, but I don't see why that would be necessary if Application_End was successfully called since SqlDependency.Stop was called there. The solution I'm resorting to right now is when the problem occurs and the web application is hosed up, I just restart its application pool manually. which sorts the entire issue out until the next time the application restarts itself. I have the application pool set to default values, so it restarts itself every 1740 minutes.
Has anyone else had a similar problem or any suggestions? I've been searching Google high and low for any information, but haven't found anything other than the usual suggestions of having to call Start in the first place. I'd like to get this issue licked as soon as possible. Thanks!
http://billrowell.com/
princeG
Star
9612 Points
1602 Posts
Re: SqlDependency Start Exception
Jul 13, 2011 05:49 AM|LINK
SQL Dependency Sample: http://msdn.microsoft.com/en-us/library/9dz445ks.aspx
http://www.codeproject.com/KB/database/chatter.aspx
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/0f77c989-d6de-49bf-9e4c-d24a60c0160f/
billr578
Member
641 Points
134 Posts
Re: SqlDependency Start Exception
Jul 13, 2011 01:29 PM|LINK
Thanks for the links, but I had already read through all of those and basically nothing there offers a solution to what I'm seeing in our application. I'll keep debugging and digging. If anyone out there has a suggestion though, I'd appreciate it.
http://billrowell.com/
Cathy Mi - M...
Member
741 Points
165 Posts
Microsoft
Re: SqlDependency Start Exception
Jul 20, 2011 04:59 PM|LINK
Hi,
What is the stack trace mentioned in the event log entry?
Thanks,
Cathy Miller
billr578
Member
641 Points
134 Posts
Re: SqlDependency Start Exception
Jul 21, 2011 01:59 PM|LINK
Hi Cathy,
The first exception occurs when I call my PreLoad method on caching data in Global.asax.cs. The stack trace is below. I see different stack traces on different pages as SqlDependency isn't available when loading data. When I woke up this morning I did notice 3 different queues for the database in question, which I find kind of odd since I only call Start/Stop on SqlDependency in Global.asax.cs. I would have thought there would only be one queue.
Also, the errors only happened this last time (last night around 2:00am EST) for about a 7-8 minute window. At some point after that window, everything with the SqlDependency started to work again.
An idea I had to curtail this issue for the time being is put a try/catch around loading the data with a dependency. If it fails, load the data without the dependency. At least I'll get my data back, even though it won't come out of the cache. As SqlDependency becomes available again, caching should resume. The only thing about this is that it doesn't solve whatever issue is at hand and may just mask a potential bigger issue.
I appreciate any help you or anyone else out there might be able to provide for me. Thanks!
-Bill
Exception information:
Exception type: InvalidOperationException
Exception message: When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
Thread information:
Thread ID: 45
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlDependency.GetDefaultComposedOptions(String server, String failoverServer, IdentityUserNamePair identityUser, String database)
at System.Data.SqlClient.SqlCommand.CheckNotificationStateAndAutoEnlist()
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Graydoor.Icarus.ToolBox.CacheControl.LoadCategoryData() in d:\wwwroot\public\frightcatalog\trunk\src\Graydoor.Icarus\ToolBox\CacheControl.cs:line 336
at Graydoor.Icarus.ToolBox.CacheControl.PreLoad() in d:\wwwroot\public\frightcatalog\trunk\src\Graydoor.Icarus\ToolBox\CacheControl.cs:line 32
http://billrowell.com/
billr578
Member
641 Points
134 Posts
Re: SqlDependency Start Exception
Jul 21, 2011 09:59 PM|LINK
It just happeend again about 30 minutes ago and I managed to find this in the SQL Server Logs at the same time our site was unavailable:
The query notification dialog on conversation handle '{A1FB449B-DEB3-E011-B6D2-002590198D55}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
After a bunch of those types of messages on different conversation handles, I see:
Query notification delivery could not send message on dialog '{DF974AF3-F0AF-E011-B6D2-002590198D55}.'. Delivery failed for notification '<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="1217214" type="change" source="timeout" info="none" database_id="5" sid="0x1F59122ACF8727448DA988D041A1D484"><qn:Message>247caab1-7cfa-4374-89ce-a9e6010c5ac7;770480f8-444b-424e-a4c7-7101a1e3963f</qn:Message></qn:QueryNotification>' because of the following error in service broker: 'The conversation handle "DF974AF3-F0AF-E011-B6D2-002590198D55" is not found.'.
Query notification delivery could not send message on dialog '{DF974AF3-F0AF-E011-B6D2-002590198D55}.'. Query notification delivery failed because of the following error in service broker: 'The conversation handle "DF974AF3-F0AF-E011-B6D2-002590198D55" is not found.'. See the error log for additional information.
Obviously all of those error messages represent some different handles, but there's a ton of them in the log around this time.
I went back to when it happened earlier in the morning and found the same type of error messages. A quick search online reveals this MSDN support article:
http://support.microsoft.com/kb/958006
We got our version of SQL Serve 2008 no more than 2 months ago, so I would think this cumulative update would be part of our install based on the wording of this article.
I was able to get our site working again by manually shutting down the applicatoin pool for our web application and then starting it back up. At that point everything seems to be fine and query notifications are working properly.
At any given time, even when our application is working fine, I see error messages in the log like the 3rd one listed in bold above. Not sure if that's normal or not.
http://billrowell.com/
Cathy Mi - M...
Member
741 Points
165 Posts
Microsoft
Re: SqlDependency Start Exception
Jul 29, 2011 04:26 PM|LINK
Hi,
These type of issues are difficult to troubleshoot in a forum thread as they require a more in-depth level of support. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
It is hard to tell, but you may need code to clean up the existing query notification subscriptions either when the application starts up or shuts down.
Thanks,
Cathy Miller