February 26, 2011 at 2:52 pm
I have generic error logging SP that I call from SPs CATCH block to log the error and then ReRaise the error for the caller to catch. It works SP to SP. But the calling C# code does not get the exception.
After a lot of tests, I have realized that if one does RAISERROR in the SP code (with out TRY/CATCH), it works, but if RAISERROR is done from within a CATCH block it doesn't!
Why? Is this a SQL 2008 Bug? Any suggested workarounds?
Here are my 2 test Stored Procs to demo the problem:-
Calling spCauseError0 from C# - no exception is thrown back to the code
Calling spCauseError1 from C# works (an exception is thrown back to the code)
ALTER PROCEDURE [dbo].[spCauseError0]
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(512)='';
BEGIN TRY
SELECT 1/0 -- Cause an error
END TRY
BEGIN CATCH
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END CATCH
END
ALTER PROCEDURE [dbo].[spCauseError1]
AS
BEGIN
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END
February 26, 2011 at 10:35 pm
Stewart,
Can you provide a short C# script please - I'd like to see that side of the code too. Also, it would help to know which version and build of SQL Server (e.g. SQL Server 2008 build 10.0.4272) you are using, as well as which driver you are using to connect (e.g. Native Client, OLEDB, ODBC). If you can provide a repro that closely matches you real scenario without being 500 lines long, that will save a lot of time and potential wrong turns.
Thanks,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 26, 2011 at 11:49 pm
Hi Paul,
I am using SQL Server 2008 (+SP2) 10.0.4000 on Windows 7 (OS=6.1.7600)
The dev env is VS2010 and I have .Net4 configured for the application.
The connection string AppConstants.Instance.strSEIFSAConnectionString (from web.config) is
connectionString="Data Source=STEWART-PC\SQLSERVER;Initial Catalog=SeifsaCRMX;Integrated Security=True" providerName="System.Data.SqlClient"
// Sample C# to Demo The issue....
// In summary if I use spCauseError1, the exception is caught here and my LogException code is executed.
// In summary if I use spCauseError0, the code executes "silently" - no exception thrown.
public void DoCauseError()
{
using (SqlConnection sqlConnection = new SqlConnection(AppConstants.Instance.strSEIFSAConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("spCauseError", sqlConnection))
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
try
{
sqlConnection.Open();
sqlCommand.ExecuteScalar();
}
catch (SqlException ex)
{
LogExceptions.Instance.LogException(0, 0, 0, 0, 0, "CommonVM:DoCauseError SqlException", ex);
throw;
}
catch (Exception ex)
{
LogExceptions.Instance.LogException(0, 0, 0, 0, 0, "CommonVM:DoCauseError", ex);
throw;
}
finally
{
sqlConnection.Close();
}
}
}
}
Thanks, Stewart
February 27, 2011 at 12:33 am
Ah right - I see it now. The SELECT 1/0 returns an empty result set. Execute Scalar only opens the first result, so your code never encounters the error in the second result. Change SELECT 1/0 to DECLARE @s-2 INT; SET @s-2 = 1/0; to see the difference. Avoid returning any result sets from the procedure before calling Execute Scalar, or use Execute Reader and call Reader.NextResult to process the packet with the error inside.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 2:27 am
Hi Paul,
I understand your point about ExecuteScalar() will only "see" one result set, so indeed if my SP contains:-
BEGIN TRY
DECLARE @s-2 INT;
SET @s-2 = 1/0;-- Cause an error
END TRY
BEGIN CATCH
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END CATCH
The sqlCommand.ExecuteScalar(); experiences the exception!
Now of course the bigger picture is that my try/catch block in the SP is to catch any SQL error, log the exception, and then report it back to the C# caller in the form of an exception.
So if the caller is using ExecuteScalar(), the exception is not seen, as you have pointed out.
So for this be general, as I really want the caller to get an exception for "any" sql error, which includes a SELECT1/0; it strikes my that my CATCH block should clear any possible result sets so that the RAISERROR is the only result? I have done some googling but I don't see how to clear the result set - is it possible?
Thanks, Stewart
February 27, 2011 at 2:47 am
stewart.gadget (2/27/2011)
I have done some googling but I don't see how to clear the result set - is it possible?
I'm afraid not. If something else in the procedure (or caused by a statement in the procedure - e.g. a trigger) has already generated a result set, it is not possible to 'clear the output buffer'. Sorry about that, you'll need to design the code differently.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 3:48 pm
Gadgetman Stewart (2/27/2011)
Hi Paul,I understand your point about ExecuteScalar() will only "see" one result set, so indeed if my SP contains:-
BEGIN TRY
DECLARE @s-2 INT;
SET @s-2 = 1/0;-- Cause an error
END TRY
BEGIN CATCH
RAISERROR( 'An Error has occurred in my SP!', 16, 1 );
END CATCH
The sqlCommand.ExecuteScalar(); experiences the exception!
Now of course the bigger picture is that my try/catch block in the SP is to catch any SQL error, log the exception, and then report it back to the C# caller in the form of an exception.
So if the caller is using ExecuteScalar(), the exception is not seen, as you have pointed out.
So for this be general, as I really want the caller to get an exception for "any" sql error, which includes a SELECT1/0; it strikes my that my CATCH block should clear any possible result sets so that the RAISERROR is the only result? I have done some googling but I don't see how to clear the result set - is it possible?
Thanks, Stewart
You could always save the result sets in a table until the very end for final retrieval.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply