Exception on SQLDependency background thread

Andrew Teece · Feb 13, 2009

We have a problem with our Windows Service that uses SQLDependency. It has
been crashing and not going through any of my exception management code.

The following is logged in the Windows Application Event log;
EventType clr20r3, P1 enate.mi.serverservice.exe, P2 1.0.0.6, P3
4991797a, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2753, P8 29, P9
system.data.sqlclient.sql, P10 NIL.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

After a crash-course in WinDBG and DebugDiag I have managed to get a memory
dump of the situation. By examining the thread with the error I found the
following exception;
Exception object: 0f66cde8
Exception type: System.Data.SqlClient.SqlException
Message: The conversation handle "68F91595-7EF7-DD11-A7C3-0011432742A1"
is not found.
Invalid object name
'SqlQueryNotificationService-5166b3f6-1909-464b-b1f9-f1fd264bb0c9'.
InnerException: <none>
StackTrace (generated):
SP IP Function
0677EFF4 656D286B
System_Data_ni!System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlClient.SqlException,
Boolean)+0x1dc4ab
0677F008 656C8DEC
System_Data_ni!System.Data.SqlClient.SqlInternalConnection.OnError(System.Data.SqlClient.SqlException,
Boolean)+0x49f51c
0677F020 652294F3
System_Data_ni!System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(System.Data.SqlClient.TdsParserStateObject)+0xc3
0677F050 6560A8D5
System_Data_ni!System.Data.SqlClient.TdsParser.Run(System.Data.SqlClient.RunBehavior,
System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader,
System.Data.SqlClient.BulkCopySimpleResultSet,
System.Data.SqlClient.TdsParserStateObject)+0x959
0677F0BC 65220F12
System_Data_ni!System.Data.SqlClient.SqlDataReader.ConsumeMetaData()+0x22
0677F0D0 65220A34
System_Data_ni!System.Data.SqlClient.SqlDataReader.get_MetaData()+0x54
0677F0FC 6521F3EA
System_Data_ni!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader,
System.Data.SqlClient.RunBehavior, System.String)+0x12a
0677F134 654F2DC0
System_Data_ni!System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()+0x6c
0677F168 654F43D0
System_Data_ni!System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(System.IAsyncResult,
System.String)+0x7c
0677F19C 654F45C9
System_Data_ni!System.Data.SqlClient.SqlCommand.EndExecuteReader(System.IAsyncResult)+0x49
0677F1CC 6562EE89
System_Data_ni!SqlDependencyProcessDispatcher+SqlConnectionContainer.AsyncResultCallback(System.IAsyncResult)+0x45
StackTraceString: <none>
HResult: 80131904

My understanding from the stack-trace is that this is not an error in my
code? It is an error in the Microsoft SQLDependency class, but because that
is running on a background thread any exceptions thrown cannot be caught by
my application. Is this correct? What can I do to handle such eventualities?

Regards

Andrew Teece
Technical Architect, eNate

Miha Markic · Feb 14, 2009

Hi Andrew,

Do you have any idea what triggers this error? What code?
If not, some sort of loggin might help.
Is it possible that this is a cross thread issue?

Miha

Cor Ligthert[MVP] · Feb 15, 2009

System.Data.SqlClient is the part of the Net namespace that does everything
around SQL server.

As far as I see that dump I only see information about that, therefore your
problem is likely in your program.

As Miha wrote, give us more information about your application.

However much better is probably as you follow the proces in SQL profiler.

Cor

Steven Cheng · Feb 16, 2009

Hi Andrew,

From the callstack you provided, the exception does get thrown at the SQL
Dependency's internal code. Currently I think we can first try to narrow
down the problem scenario, possible causes are:

** configuration issue at sql server side

** application context, whether the problem is specific to windows service
running context

for the second one, you can try running the same code in a console or
winform app to see whether the same error occured. And you can refer to
some other tech article to see whether there is anything missed for
configuring the sqldependency

#Using SqlDependency for data change events
http://www.codeproject.com/KB/database/chatter.aspx?display=PrintAll&fid=235
028&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2053728&fr=26

In addition, as for the "The conversation handle [xx] is not found." error,
I've found blog entry documented this:

#SqlDependancy.Start and Conversation Handle Not Found
http://www.hanselman.com/blog/SqlDependancyStartAndConversationHandleNotFoun
d.aspx

You can also have a try on the suggestion there.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

Andrew Teece · Feb 17, 2009

Cor,
You only see information about the system.data.sqlclient because that is
in control of the thread that the exception occured on.

My code is running fine. The problem is that if an exception is thrown
in this part of MS code there is nothing I can do to capture/handle the
error because it is thrown on a thread that I didnt start!

Andrew

Andrew Teece · Feb 17, 2009

Miha,
I have been running though my code a lot recently with regards this
issue.

This could be a cross thread issue; and I think I know what is going on.
I will post in my reply to Steven below.

Andrew

Andrew Teece · Feb 17, 2009

Steven,
I think I have narrowed down the problem scenario. It happens (or was
happening) when my exception handling code raised an exception. I am not
100% sure how this caused a problem though.

We have a hosting application that hosts our "MessagingInfrastucture"
component. This component has a SQLDependency permanently listening. When
the SQLDependency triggers we start another thread that executes an "Engine"
to process the "message" that it received. There is only 1 entry point into
the "Engine" so it is fully exception handled.

We had a bug that meant that in some circumstances the exception
handling itself raised an exception. When the MessagingInfrastructure
component is disposed we call SQLDependency.Stop to tidy up properly. This
is called when the application ends correctly and obviously when an
unhandled exception is thrown.

Could it be that in some way SQLDependency.Stop is deleting the
conversation from SQL even though the thread is still listening? If that is
the case though I don't understand why this is only a problem when an
exception is thrown on another thread, surely it would be thrown on any
SQLDependency.Stop call.

Is it not a slight design oversight that it is possible for the MS code
to thrown an exception that I have no way (that i know of) of
catching/handling?

Andrew

"Steven Cheng" said:
Hi Andrew,

From the callstack you provided, the exception does get thrown at the SQL
Dependency's internal code. Currently I think we can first try to narrow
down the problem scenario, possible causes are:

** configuration issue at sql server side

** application context, whether the problem is specific to windows service
running context

for the second one, you can try running the same code in a console or
winform app to see whether the same error occured. And you can refer to
some other tech article to see whether there is anything missed for
configuring the sqldependency

#Using SqlDependency for data change events
http://www.codeproject.com/KB/database/chatter.aspx?display=PrintAll&fid=235
028&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2053728&fr=26

In addition, as for the "The conversation handle [xx] is not found."
error,
I've found blog entry documented this:

#SqlDependancy.Start and Conversation Handle Not Found
http://www.hanselman.com/blog/SqlDependancyStartAndConversationHandleNotFoun
d.aspx

You can also have a try on the suggestion there.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support
Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------

From: "Andrew Teece" <[email protected]>
Subject: Exception on SQLDependency background thread
Date: Fri, 13 Feb 2009 12:15:00 -0000

Click to expand...

We have a problem with our Windows Service that uses SQLDependency. It has
been crashing and not going through any of my exception management code.

The following is logged in the Windows Application Event log;
EventType clr20r3, P1 enate.mi.serverservice.exe, P2 1.0.0.6, P3
4991797a, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2753, P8 29, P9
system.data.sqlclient.sql, P10 NIL.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

After a crash-course in WinDBG and DebugDiag I have managed to get a
memory
dump of the situation. By examining the thread with the error I found the
following exception;
Exception object: 0f66cde8
Exception type: System.Data.SqlClient.SqlException
Message: The conversation handle
"68F91595-7EF7-DD11-A7C3-0011432742A1"
is not found.
Invalid object name
'SqlQueryNotificationService-5166b3f6-1909-464b-b1f9-f1fd264bb0c9'.
InnerException: <none>
StackTrace (generated):
SP IP Function
0677EFF4 656D286B
System_Data_ni!System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlC lient.SqlException,
Boolean)+0x1dc4ab
0677F008 656C8DEC
System_Data_ni!System.Data.SqlClient.SqlInternalConnection.OnError(System.D ata.SqlClient.SqlException,
Boolean)+0x49f51c
0677F020 652294F3
System_Data_ni!System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Sys tem.Data.SqlClient.TdsParserStateObject)+0xc3
0677F050 6560A8D5
System_Data_ni!System.Data.SqlClient.TdsParser.Run(System.Data.SqlClient.Ru nBehavior,
System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader,
S

Click to expand...

Steven Cheng · Feb 19, 2009

Thanks for your reply Andrew,

Yes, agree that the problem is somewhat related to the SqlDependency's
internal implementation(such as connection management or status checking)
and your problem condition somewhat cause its internal code logic to fail.
Unfortunately, for exception raised in separate thread that we have no
control(no exception handling on that thread), there is no means for other
thread to capture and supress it. Also, start from .NET 2.0, any unhandled
exception in a thread will lead to the terminate of entire process(new
exception handling policy).

#Exceptions in Managed Threads
http://msdn.microsoft.com/en-us/library/ms228965.aspx

For this issue, is it possible for you to generate a simplified
project(such as a console and a test database table) to repro the problem?
If so, I'd be glad to help do some further investigation on my side since
I'm also quite interested in this problem behavior.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

--------------------

From: "Andrew Teece" <[email protected]>
References: <u4#[email protected]>

Steven Cheng · Feb 25, 2009

Hi Andrew,

Have you got any progress on this issue? If still need help on this, please
feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

Kenny Nguyen · Apr 20, 2009

So does anyone have any solution to this error? My service crashes like crazy
with the same error and I have no idea why?!

Thanks,
Kenny.

Exception on SQLDependency background thread

Andrew Teece

Miha Markic

Cor Ligthert[MVP]

Steven Cheng

Andrew Teece

Andrew Teece

Andrew Teece

Steven Cheng

Steven Cheng

Kenny Nguyen