There was a massive upgrade to the error handling within SQL Server 2005. These articles are fairly extensive: Error Handling in SQL 2005 and Later by Erland Sommarskog and Error Handling in SQL 2000 – a Background by Erland Sommarskog
The best way is something like this:
Create your stored procedure like:
CREATE PROCEDURE YourProcedure
AS
BEGIN TRY
BEGIN TRANSACTION --SqlTransaction
DECLARE @ReturnValue int
SET @ReturnValue=NULL
IF (DAY(GETDATE())=1 --logical error
BEGIN
SET @ReturnValue=5
RAISERROR('Error, first day of the month!',16,1) --send control to the BEGIN CATCH block
END
SELECT 1/0 --actual hard error
COMMIT TRANSACTION --SqlTransaction
RETURN 0
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION --only rollback if a transaction is in progress
END
--will echo back the complete original error message to the caller
--comment out if not needed
DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int
SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)
RETURN ISNULL(@ReturnValue,1)
END CATCH
GO
however that is only for SQL Server 2005 and up. Without using the TRY-CATCH blocks in SQL Server 2005, you have a very difficult time removing all of the messages that SQL Server sends back. The extra messages you refer to are caused by the nature of how rollbacks are handled using @@trancount:
from http://www.sommarskog.se/error-handling-I.html#trancount
@@trancount is a global variable which
reflects the level of nested
transactions. Each BEGIN TRANSACTION
increases @@trancount by 1, and each
COMMIT TRANSACTION decreases
@@trancount by 1. Nothing is actually
committed until @@trancount reaches 0.
ROLLBACK TRANSACTION rolls back
everything to the outermost BEGIN
TRANSACTION (unless you have used the
fairly exotic SAVE TRANSACTION), and
forces @@trancount to 0, regards of
the previous value.
When you exit a stored procedure, if
@@trancount does not have the same
value as it had when the procedure
commenced execution, SQL Server raises
error 266. This error is not raised,
though, if the procedure is called
from a trigger, directly or
indirectly. Neither is it raised if
you are running with SET IMPLICIT
TRANSACTIONS ON
If you don't want to get the warning about the transaction count not matching, you need to only have one transaction open at any one time. You do this by creating all of your procedure like this:
CREATE PROC YourProcedure
AS
DECLARE @SelfTransaction char(1)
SET @SelfTransaction='N'
IF @@trancount<0
BEGIN
SET @SelfTransaction='Y'
BEGIN TRANSACTION --SqlTransaction
END
SELECT 1/0
IF @@ERROR<> 0
BEGIN
IF @SelfTransaction='Y'
BEGIN
ROLLBACK TRANSACTION --SqlTransaction
END
RETURN -1
END
ELSE
BEGIN
IF @SelfTransaction='Y'
BEGIN
COMMIT TRANSACTION --SqlTransaction
END
RETURN 0
END
GO
By doing this, you only issue the transaction commands if you are not already in a transaction. If you code all of your procedures this way, only the procedure or the C# code that issues the BEGIN TRANSACTION will actually issue the COMMIT/ROLLBACK and the transaction counts will always match (you won't get an error).
in C# from TransactionScope Class Documentation:
static public int CreateTransactionScope(
string connectString1, string connectString2,
string commandText1, string commandText2)
{
// Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
System.IO.StringWriter writer = new System.IO.StringWriter();
try
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open();
// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
// If you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources as connection2 is opened
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(connectString2))
{
// The transaction is escalated to a full distributed
// transaction when connection2 is opened.
connection2.Open();
// Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(commandText2, connection2);
returnValue = command2.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
}
}
}
// The Complete method commits the transaction. If an exception has been thrown,
// Complete is not called and the transaction is rolled back.
scope.Complete();
}
catch (TransactionAbortedException ex)
{
writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}
catch (ApplicationException ex)
{
writer.WriteLine("ApplicationException Message: {0}", ex.Message);
}
// Display messages.
Console.WriteLine(writer.ToString());
return returnValue;
}
Just a thought, but you might be able to use the TransactionAbortedException catch to get the actual error and ignore the transaction count mismatch warning.