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

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

  • Re: Ignore SQL Server 2000 store proc errors and still get results?
    ... is occurring in ADO.NET when I try to execute that stored procedure. ... > The problem is in the way you are handling errors in your sproc code. ... > DECLARE @Rows int ... >> BEGIN SETUP TEST - run this script in Query Analyzer ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Intercepting invalid parameters to a stored procedure
    ... cuts up the query string into pieces and barks if the input ... idiot-proof stored procedure. ... @OrderNumber int output ... SET @OrderNumber = @@IDENTITY ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: How to retrieve stored procedure error code in C#?
    ... @Fred int OUTPUT ... >>> I am able to view these error codes when I run the stored proc in Query ... >>> have seen some mention of the stored procedure having to stuff it into ...
    (microsoft.public.dotnet.framework.adonet)
  • Please Help before I pull out the last of my hair
    ... @ConsultantID Int, ... INNER JOIN PartyGuestLineItem AS PGLI WITH (NOLOCK) ... -- RETURN a recordset with a summary of the products purchased by the ... However when I run the stored procedure as shown here in an ASP page I get ...
    (microsoft.public.inetserver.asp.db)
  • TableAdapter, INNER JOINs, stored procs, and problems with Update
    ... I have a stored procedure that uses JOINs to return columns from multiple ... I also have another stored proc that that takes a series of params ... @ac2 int, ...
    (microsoft.public.dotnet.framework.aspnet)