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
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"
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:
[email protected]" in the query).
Thanks,
Mike
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"
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
