Re: Ignore SQL Server 2000 store proc errors and still get results?

In my "real" code I am actually saving @@ERROR and @@ROWCOUNT into variables
exactly as you have shown. The sample I mailed you didn't just because I
had a simplified version to demonstrate what I was running in to. In the
case of my sample, @@ERROR is being caught properly because I am accessing
it immediately after the SELECT statement. If you execute my spTestGetData
from Query Analyzer in the context that is described in my post, you'll see
that the error condition is being caught properly, that a result set of 2
rows is being returned and an error is being raised. So the problem isn't
in the SQL code. It's doing exactly what I'd expect it to do. The problem
is occurring in ADO.NET when I try to execute that stored procedure. If an
error occurs in the context of the stored procedure (which is what happens
in this case when a particular row has a lock on it from another process),
ADO.NET throws an error and I don't get back any of my result set. The C#
code that I included demonstrates that.

This link http://www.sommarskog.se/error-handling-I.html#ADO.Net has a part
that says that the only way that I can do what I'm trying to do is to use
the Odbc versions of the ADO.NET stuff and not the Sql or OleDb. It doesn't
sound too "certain" though. I was hoping to get a more certain method of
being able to get result sets back to ADO.NET when errors occur in the
procedure. If I can't, I'm going to have to resort to doing the logic on
the client and making multiple trips to the server, which I'd rather not do
especially in the environment where this will be running.

Thanks,
Mike




"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:4hqpf196an4r7htjnlro1kluhb9an1gbaq@xxxxxxxxxx
> The problem is in the way you are handling errors in your sproc code.
> @@error is transient and gets reset to zero on the next statement. Its
> value needs to be captured in a local variable which is then tested
> because you may need to branch based on what you find. It's possible
> that a data modification operation may not succeed and also may not
> result in an error, so you need to also check @@rowcount. Error
> handling code in sprocs should look something like the following:
>
> SET NOCOUNT ON
> DECLARE @Err int
> DECLARE @Rows int
>
> -- Code that does data modification here, omitted for brevity.
>
> -- Capture errors and rows affected.
> SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT
>
> IF @Rows > 0
> -- Means at least partial success if no explicit
> -- transaction involved.
> ELSE
> -- No rows were affected, you may or may
> -- not have errors.
>
> IF (@Err <> 0)
> BEGIN
> -- Code here to deal with error(s).
> END
>
> --Mary
>
> On Fri, 12 Aug 2005 11:30:48 -0400, "Mike Jansen"
> <mjansen_nntp@xxxxxxxx> wrote:
>
>>That's exactly what I am doing but the errors raised by lock timeouts
>>(even
>>though I'm handling them) are still causing an exception to be thrown in
>>.NET before I get the result sets using SqlDataReader or SqlDataAdapter.
>>
>>Try this:
>>
>>/**********************************************\
>> BEGIN SETUP TEST - run this script in Query Analyzer
>>\**********************************************/
>>
>>CREATE TABLE TestError
>>(
>> Pk int NOT NULL PRIMARY KEY CLUSTERED,
>> Data char(100) NOT NULL
>>)
>>GO
>>
>>CREATE PROCEDURE spTestGetData
>>AS
>>SET NOCOUNT ON
>>SET LOCK_TIMEOUT 0
>>DECLARE @result table (Pk int)
>>DECLARE @pk int
>>DECLARE @data char(100)
>>
>>-- Should get this row
>>SELECT @pk = Pk, @data = Data FROM TestError WHERE Pk = 1
>>IF @@ERROR = 0
>> INSERT @result (Pk) VALUES (@Pk)
>>
>>-- Should error on this row and not insert since its locked
>>SELECT @pk = Pk, @data = Data FROM TestError WHERE Pk = 6
>>IF @@ERROR = 0
>> INSERT @result (Pk) VALUES (@Pk)
>>
>>-- Should get this row
>>SELECT @pk = Pk, @data = Data FROM TestError WHERE Pk = 12
>>IF @@ERROR = 0
>> INSERT @result (Pk) VALUES (@Pk)
>>
>>SELECT T.Data FROM TestError T JOIN @result R ON T.Pk = R.Pk
>>GO
>>
>>
>>INSERT TestError (Pk, Data) VALUES (1, 'A')
>>INSERT TestError (Pk, Data) VALUES (2, 'AA')
>>INSERT TestError (Pk, Data) VALUES (3, 'AAA')
>>INSERT TestError (Pk, Data) VALUES (4, 'AAAA')
>>INSERT TestError (Pk, Data) VALUES (5, 'AAAAAA')
>>INSERT TestError (Pk, Data) VALUES (6, 'AAAAAAA')
>>INSERT TestError (Pk, Data) VALUES (7, 'AAAAAA')
>>INSERT TestError (Pk, Data) VALUES (8, 'AAAAA')
>>INSERT TestError (Pk, Data) VALUES (9, 'AAAA')
>>INSERT TestError (Pk, Data) VALUES (10, 'AAA')
>>INSERT TestError (Pk, Data) VALUES (11, 'AA')
>>INSERT TestError (Pk, Data) VALUES (12, 'A')
>>
>>GO
>>
>>/**********************************************\
>> END SQL SETUP TEST
>>\**********************************************/
>>
>>
>>/**********************************************\
>> BEGIN C# -- make this into a console app
>>\**********************************************/
>>using System;
>>using System.Data;
>>using System.Data.SqlClient;
>>
>>namespace TestErrors
>>{
>> class Test
>> {
>> [STAThread]
>> static void Main(string[] args)
>> {
>> SqlConnection connection = new
>>
>> SqlConnection("Server=(local);Database=Test;Trusted_Connection=Yes");
>> SqlCommand command = new SqlCommand("spTestGetData", connection);
>> SqlDataReader reader = null;
>>
>> try
>> {
>> command.CommandType = CommandType.StoredProcedure;
>> Console.WriteLine("Open connection");
>> connection.Open();
>> Console.WriteLine("Execute reader");
>> reader = command.ExecuteReader();
>> Console.WriteLine("Start reading");
>> do
>> {
>> while (reader.Read())
>> {
>> Console.WriteLine("Row: {0}",
>> (reader.IsDBNull(0) ? "null" : reader.GetValue(0)));
>> }
>> } while (reader.NextResult());
>>
>> Console.WriteLine("Made it to the end!");
>> }
>> catch (Exception ex)
>> {
>> Console.WriteLine("Exception: {0}", ex.ToString());
>> }
>> finally
>> {
>> if (reader != null)
>> reader.Close();
>>
>> if (command != null)
>> command.Dispose();
>>
>> if (connection != null)
>> connection.Dispose();
>> }
>>
>> }
>> }
>>}
>>
>>/**********************************************\
>> END C#
>>\**********************************************/
>>
>>
>>/**********************************************\
>> The actual test
>>\**********************************************/
>>1. Run the setup script
>>2. Create the console app
>>3. Execute the following in Query Analyzer to lock record pk=6
>> BEGIN TRANSACTION
>> UPDATE TestError SET Data = 'SSSSSSS' WHERE Pk = 6
>>4. Execute this in a different Query Analyzer window (different
>>connection)
>> to see what the procedure returns. You should get an error AND
>>results (2 rows)
>> EXEC spTestGetData
>>5. Run the console app. The exception gets thrown before the results are
>>retrieved.
>>
>>Thanks,
>>Mike
>>
>>
>>"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
>>news:24dpf117cc2uldfuic7nceo5jah08he0vf@xxxxxxxxxx
>>> You definitely don't want to do 30 round trips by handling the logic
>>> in client code. Another option would be to create a table variable or
>>> temp table in your stored procedure to handle the collection of valid
>>> rows. All errors are appropriately handled or ignored in your T-SQL
>>> code. Once the processing of these rows is complete, you simply select
>>> from the table variable or temp table, and you have your result set
>>> returned to the client in a single RT. Sorry, I don't have time to
>>> look at your app, but this approach might work well for you since it
>>> efficiently encapsulates all processing and error handling in
>>> server-side code.
>>>
>>> --Mary
>>>
>>> On Thu, 11 Aug 2005 14:37:01 -0400, "Mike Jansen"
>>> <mjansen_nntp@xxxxxxxx> wrote:
>>>
>>>>I'm not calling RAISERROR. Errors occur because I have SET LOCK_TIMEOUT
>>>>0
>>>>and I skip past records that are locked (I'm not using the READPAST
>>>>hint
>>>>because it doesn't fit our scenario).
>>>>
>>>>An alternative is to have the stored procedure only do one record at a
>>>>time
>>>>and have all the looping done entirely in the client (which is actually
>>>>a
>>>>service). I was trying to avoid this to eliminate 30 round trips per
>>>>invocation. If you are interested in the "big picture" of what I'm
>>>>trying
>>>>to do, I have posted it for sanity check on
>>>>microsoft.public.sqlserver.programming (look for "Quasi-work table
>>>>(sanity
>>>>check)", if you Google use "author:mjansen_nntp@xxxxxxxx" in the query).
>>>>
>>>>Thanks,
>>>>Mike
>>>>
>>>>
>>>>"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
>>>>news:ghpkf1l0pq3853o99306javp3578jremo7@xxxxxxxxxx
>>>>> Do you have control over the stored procedures? The optimal way to
>>>>> handle server-side errors in this situation would be to NOT use
>>>>> raiserror and instead return the expected @@error values in output
>>>>> parameters instead. That way you can decide in your client-side code
>>>>> whether or not to ignore the output parameter values without
>>>>> triggering an exception. Raising errors and handling exceptions is
>>>>> expensive in both server and client code.
>>>>>
>>>>> --Mary
>>>>>
>>>>> On Wed, 10 Aug 2005 14:39:30 -0400, "Mike Jansen"
>>>>> <mjansen_nntp@xxxxxxxx> wrote:
>>>>>
>>>>>>We have a SQL Server 2000 stored procedure that raises errors when
>>>>>>run.
>>>>>>Because of what it is doing, this is expected. However, we have
>>>>>>result
>>>>>>sets
>>>>>>that are also being returned from the stored procedure.
>>>>>>
>>>>>>Using either DataAdapter or SqlDataReader, we'd like to be able to
>>>>>>execute
>>>>>>the stored proc and still get the result sets back. However,
>>>>>>exceptions
>>>>>>are
>>>>>>thrown whenever the stored proc raises an error. Is there any way to
>>>>>>ignore
>>>>>>or suppress these errors and still get the result sets?
>>>>>>
>>>>>>Thanks,
>>>>>>Mike
>>>>>>
>>>>>
>>>>
>>>
>>
>


.

Relevant Pages