How can I grab results from a query even if an SQL exception occurs during command.executeReader()?

How can I grab results from a query even if an SQL exception occurs during command.executeReader()?

I have this slice of code that works sometimes and sometimes not. The query that is being executed has not been written by myself, but I am told that all I have to do is run it against the database to get the results I need. Here is my code:

try {   using (SqlDataReader dr = cmd.ExecuteReader()) {     while (dr.Read()) {       try {         list.Add(dr.GetString(3) + " " + dr.GetInt32(4).ToString() + " " + dr.GetString(5) + " " + dr.GetDecimal(8).ToString());       } catch (Exception e) {         list.Add("fail");       }     }   } } catch (SqlException sqlException) {   Debug.WriteLine(sqlException.Message); } 

The error that I sometimes receive is that I cannot drop a table because I do not have permission or the table does not exist. Other times the query executes without any problems and I am able to retrieve and store my results in a list.

When I run the query in SQL Server 2008, sometimes I do indeed get errors but the results still show up and are what I expect. So my question is: How can I grab these results regardless of the error that seems to come up whenever it feels like?

Here is a tiny piece of the query that is causing me my troubles:

IF  EXISTS (SELECT * from tempdb..sysobjects where name like '#TABLE%')   DROP #TABLE 

There are many of these if statements in the query that i am running and it's unpredictable which one is going to cause an error. So what i have done for right now is surround the DROP #TABLE in a try-catch block so that at least i can still get retrieve the results in my silverlight program. I will get with my higher up and ask him why the query is returning these errors spontaneously..

Answers & Comments...

Answer: 1

EDIT

Never mind, I see the problem. I forgot that temp table names are appended with additional characters to prevent naming conflicts between different processes (e.g. #TABLE___...___0000000064E2).

My guess is that the stored proc is creating a temp table named "#TABLE" under certain circumstances and the bit of SQL code you posted is meant to perform the cleanup. When running by itself, this probably works fine.

The problems probably begin to materialize when the stored proc is run by several clients simultaneously, or if one of the previous queries somehow failed to perform the cleanup (perhaps due to an error halfway through). Under those circumstances, a false positive is created (where the stored proc thinks the cleanup is necessary, but it's actually seeing the temp table created by another process). Then you get the error because there's no #TABLE associated with the current process.

In any case, wrapping the statement in a try-catch seems like a decent approach. A better approach would involve an overhaul of your code, perhaps setting a flag of some sort to help indicate that a cleanup is necessary or using a common table and some sort of transaction key (deleting all records associated with the current transaction, rather than dropping the table).

You might also want to consider using a table variable instead of a temp table.

See:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/02337dd5-5cfd-40d8-b529-12dc557d6a7e/

Or, you might also consider skipping the DROP statement entirely:
Temporary Table Scope?

---

To answer your original question, I am not aware of any way to retrieve results from a SQL Query once it has thrown an exception, not via the .NET assemblies, anyway. SQL Server Management Studio uses some heavy-duty, custom-made APIs which probably would not be worth your trouble to learn and use.

---

Ignore the following
(Preserved for reference)

If you can, try changing the SQL Query to

IF  EXISTS (SELECT * from tempdb..sysobjects where name = '#TABLE') DROP #TABLE 

(Changed like '#TABLE%' to = '#TABLE')

The like statement doesn't make any sense... it doesn't matter if there are other tables starting with "#TABLE"... you only want to know if there's a table named exactly "#TABLE".

My guess is that this is one of those cases where the logic was changed, but only half-way, maybe by two different people.

by : Cyborgx37http://stackoverflow.com/users/211627

---