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

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?
    ... You definitely don't want to do 30 round trips by handling the logic ... >An alternative is to have the stored procedure only do one record at a time ... >> handle server-side errors in this situation would be to NOT use ... >> expensive in both server and client code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Practical error/exception handling...
    ... Could I humbly suggest that the requirement for a 'standard' way of handling ... But I only need the exception handler in ... > and is informative to both user and developer when errors occur. ...
    (alt.comp.lang.borland-delphi)
  • Re: Error handling - Resume vs Exit
    ... I always wondered why Access wizards created simple exception ... handling code in the fashion they do. ... Use a separate subroutine for each try block. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Timeout on populating a datagrid
    ... I'd review the stored procedure SQL in a query tool and see how long ... An unhandled exception occurred during the execution of the ... >Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior ...
    (microsoft.public.dotnet.framework.aspnet)
  • SqlDataAdapter.Fill returns results of previous command
    ... This code executes successfully at certain points in the application, ... I don't know what the cause of this exception is yet, ... stored procedure was indeed executed on the database. ... The trace shows that the last action on the db was the stored ...
    (microsoft.public.dotnet.framework.adonet)