T-SQL Query always returns results in SSMS, but occasionally fails to return results when executed through .NET via ADO.NET

views:

answers:

+2 Q:

T-SQL Query always returns results in SSMS, but occasionally fails to return results when executed through .NET via ADO.NET

I am more than a little confused with an issue I have encountered 3 times in the past 2-3 months. The title of this question outlines the issue, but for more detail:

I have an SP which always returns results without issue when executed through SQL Server Mgmt. Studio, however very rarely (but enough to cause major headaches)- it will simply NOT return anything when it is called through a .NET console app.

I've checked the SQL Server and Windows Server error/application logs that I know of (SQL Server Logs and everything in Event Viewer on the Windows Server in which the SQL Server resides) and nothing appears amiss...

What in the heck could be the cause of this? The app, SP, and everything work just fine. It's just that when called from the .NET app, this SP fails to return data. I know the obvious solution would be to debug from VS and see the details of the connection (I also get no Exception when this happens) in the debugger from beginning to end of the SP execution, but this is not possible as the issue occurs out of the blue and is not reproducible. I want to prevent it from happening again.

Is this a possible spontaneously dropped SQL connection or what? Any thoughts, comments, and suggestions are greatly appreciated.

+1 A:

A common issue here is the SET options that are in play. This is not obvious, since you rarely set them explicitly from either SSMS or ADO.NET, but they can be different. In particular this can impact anything involving persisted indexed columns, sql xml, and a range of other behaviours; null-equality, concat-null, max-rows, schema-only (no data), interpretation of quotes, arith-abort, etc.

So: find what SET options are in play.

This can be an even bigger problem is some misbehaving code (either in the DB or the calling client) sets an option for some purpose and doesn't set it back. Especially if that code only runs occasionally.

Marc Gravell 2010-08-11 21:33:49

I think by default the only different one is `SET ARITH_ABORT` but the behaviour of that is now the same if `ANSI_WARNINGS` is on (which both set on).

Martin Smith 2010-08-11 21:38:19

Also- nothing dynamic- the SP is running the same way each time it runs.The only common thread I have identified is that this only occurs when the database server and database on which the SP resides is experiencing a high level of activity. When the server is near idle- this problem never occurs.Part of the problem is that the .NET app uses a proprietary ADO.NET wrapper which hides a lot of the SqlCommand info I need to inspect. I will need to attach and debug that.Thanks for all the responses- they got me thinking about all angles of this peculiar case of rarely vanishing ADO results.

JackFrost 2010-08-12 20:54:08

IS thing thing dynamic at all? It could be you are creating a sql statement that doesn't work in some cases.

Profiling is the only thing to do to catch it in the act. See what values it sends when it doesn't behave as expected.

HLGEM 2010-08-11 21:38:14

Thanks for the comments.I have the following standard options set directly within the SP:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOI have run profiler during a period when the .NET app was unable to get the results and everything completes executes on the SQL end. It's just that the .NET app seems to "lose" the returned results. I have also debugged and determined that the results simply seem to vanish.

JackFrost 2010-08-12 20:47:15

ansaurus

tags:

views:

answers:

T-SQL Query always returns results in SSMS, but occasionally fails to return results when executed through .NET via ADO.NET

related questions