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

I've updated my C# code to use Odbc and it works perfectly. So that link
was correct. I just need to be sure I don't have any issues regarding using
ODBC instead of SqlClient for this case.

Thanks,
Mike


/****************************************************\
Update C# with ODBC and SqlClient
\****************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;

namespace TestErrors
{
class Test
{
[STAThread]
static void Main(string[] args)
{

const string SERVER = "(local)";
const string DATABASE = "Test";
const bool USE_ODBC = true;

IDbConnection connection = null;
IDbCommand command = null;
IDataReader reader = null;

if (USE_ODBC)
{
string connectionString = string.Format(
"Driver={{SQL
Server}};Server={0};Trusted_Connection=yes;Database={1};",
SERVER, DATABASE);
connection = new OdbcConnection(connectionString);
command = new OdbcCommand();
}
else
{
string connectionString = string.Format(
"Server={0};Database={1};Trusted_Connection=Yes",
SERVER, DATABASE);
connection = new SqlConnection(connectionString);
command = new SqlCommand();
}


try
{
command.CommandText = "spTestGetData";
command.Connection = connection;
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();
}

}
}
}



"Mike Jansen" <mjansen_nntp@xxxxxxxx> wrote in message
news:e7xazK3nFHA.860@xxxxxxxxxxxxxxxxxxxxxxx
> 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