February 2, 2010 at 8:10 am
We started rewriting some stored procedures to handle errors more easliy with TRY and CATCH statements. However, the following code does not work:
BEGIN TRANSACTION
DECLARE @STRNVARCHAR(MAX)
DECLARE @amount DECIMAL(18,2)
SELECT @STR = '13,00'
BEGIN TRY
SELECT @amount = CONVERT(DECIMAL(18,2), @STR)
END TRY
BEGIN CATCH
SELECT @amount = NULL
END CATCH
INSERT INTO
tbl__temp
(
a
)
VALUES
(
@amount
)
COMMIT TRANSACTION
The error message returned is:
Msg 3930, Level 16, State 1, Line 14
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3930, Level 16, State 1, Line 24
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
That is, SQL Server decides that the transaction is doomed and cannot be commited. Does anybody know how to handle things like this better?
(I know that ISNUMERIC but this is just an example of how TRY and CATCH can be used to more complex tasks. Also, as a note, ISNUMERIC for "13,00" returns TRUE (at least for Swedish collation) but the conversion still does not work.)
/Ricky
February 2, 2010 at 8:28 am
ricky i'm just confirming what you are seeing; for me so far, if the try catch is in a transaction or using a transaction, i get the same failure; I tried a couple of variations, like the transaction is inside the TRY, etc.
but without a transaction i do get the NULL inserted into my table.
Lowell
February 2, 2010 at 8:35 am
The conversion error (severity 16) puts your transaction into an uncommittable state. From BOL:
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.
For more information about uncommittable transactions and the XACT_STATE function, see Using TRY...CATCH in Transact-SQL and XACT_STATE (Transact-SQL).
A solution is to start the transaction just before the INSERT statement, i.e.
DECLARE @STR NVARCHAR(MAX)
DECLARE @amount DECIMAL(18,2)
SELECT @STR = '13.00'
BEGIN TRY
SELECT @amount = CONVERT(DECIMAL(18,2), @STR)
END TRY
BEGIN CATCH
SELECT @amount = NULL
END CATCH
BEGIN TRANSACTION
INSERT INTO
tbl__temp
(
a
)
VALUES
(
@amount
)
COMMIT TRANSACTION
Edit: removed SELECT ERROR_SEVERITY() from the CATCH section
February 3, 2010 at 2:13 am
Thank you for replying.
I cannot set the transaction there. In my live example, the transaction is much larger, it handles wether a whole file of data should be imported or not. I have a text file with a few hundred lines of data. It is read into C#.NET, then each line is passed on to a stored procedure. If one of the lines fail, I want to rollback the whole transaction, so the transaction is created before I start reading the first line of data. The stored procedure has the issue discribed above.
Does anybody know any other solution to the TRY / CATCH problems?
/Ricky
February 3, 2010 at 4:22 am
I tried a new approach, where I started a nested transaction to solve the problem but since SQL Server does not support rollback of inner transactions without rolling back the outer transactions, it does not work either.
Does anybody use TRY/CATCH or nested transactions in SQL Server. They seem hopelessly worthless!?
/Ricky
February 3, 2010 at 4:31 am
The best I can come up with is:
BEGIN TRANSACTION
DECLARE @STR NVARCHAR(MAX)
DECLARE @amount DECIMAL(18,2)
SELECT @STR = '13,00'
BEGIN TRY
IF ISNUMERIC(@str)=1
BEGIN
SELECT @amount = CONVERT(DECIMAL(18,2), @STR)
END
ELSE
BEGIN
SELECT @amount = NULL
END
END TRY
BEGIN CATCH
SELECT @amount = NULL
END CATCH
INSERT INTO
tbl__temp
(
a
)
VALUES
(
@amount
)
COMMIT TRANSACTION
...but it still does not work since the conversion fails. I guess I have to add my own ISNUMERICAL and skip the TRY/CATCH since it has no use. Are things really this bad?
(Sorry about the negative approach but I am so frustrated! This is just ONE simple example where I have this problem...)
/Ricky
February 3, 2010 at 4:31 am
February 3, 2010 at 5:02 am
I was just trying out the use of nested transactions when I saw your reply. Too bad. I must agree the TRY..CATCH seems to be pretty worthless in this case. A little bit weird that an erroneous assignment to a variable puts your transaction in an uncommittable state especially with a TRY..CATCH block. I'm sorry, I have no other ideas right now.
February 3, 2010 at 5:10 am
Your problem is that your provided string value contains a comma in stead of a decimal !
This causes your convert statement to fail !
DECLARE @STR NVARCHAR(MAX)
DECLARE @amount DECIMAL(18, 2)
SELECT @STR = '13,00'
BEGIN TRY
SELECT @amount = CONVERT(DECIMAL(18, 2), replace(replace(@str,'.',''),',','.'))
END TRY
BEGIN CATCH
SELECT @amount = NULL
END CATCH
INSERT INTO tbl__temp ( a )
VALUES ( @amount )
commit tran
Another reason for your applications to use the correct data type and store your data without being affected by client settings !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2010 at 5:15 am
I know. That was the point of the post... that Try/Catch cannot be used to verify incoming data.
/Ricky
February 3, 2010 at 5:18 am
As Lowell was saying, everything works fine as long as you are not in a transaction. Then the try/catch works just fine... but inside a transaction, things like this makes the transaction Doomed and Uncommitable. If one could only tell SQL Server to "Un-doom" the transaction. Anybody heard about that? I cannot find anything...
/Ricky
February 3, 2010 at 5:36 am
I was just looking up the documentation of SAVE TRANSACTION in BOL and now I'm really confused. It gives an example of just what you are trying to do:
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.objects
WHERE name = N'SaveTranExample')
DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
@InputCandidateID INT
AS
-- Detect if the procedure was called
-- from an active transaction and save
-- that for later use.
-- In the procedure, @TranCounter = 0
-- means there was no active transaction
-- and the procedure started one.
-- @TranCounter > 0 means an active
-- transaction was started before the
-- procedure was called.
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
-- Procedure called when there is
-- an active transaction.
-- Create a savepoint to be able
-- to roll back only the work done
-- in the procedure if there is an
-- error.
SAVE TRANSACTION ProcedureSave;
ELSE
-- Procedure must start its own
-- transaction.
BEGIN TRANSACTION;
-- Modify database.
BEGIN TRY
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
-- Get here if no errors; must commit
-- any transaction started in the
-- procedure, but not commit a transaction
-- started before the transaction was called.
IF @TranCounter = 0
-- @TranCounter = 0 means no transaction was
-- started before the procedure was called.
-- The procedure must commit the transaction
-- it started.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- An error occurred; must determine
-- which type of rollback will roll
-- back only the work done in the
-- procedure.
IF @TranCounter = 0
-- Transaction started in procedure.
-- Roll back complete transaction.
ROLLBACK TRANSACTION;
ELSE
-- Transaction started before procedure
-- called, do not roll back modifications
-- made before the procedure was called.
IF XACT_STATE() <> -1
-- If the transaction is still valid, just
-- roll back to the savepoint set at the
-- start of the stored procedure.
ROLLBACK TRANSACTION ProcedureSave;
-- If the transaction is uncommitable, a
-- rollback to the savepoint is not allowed
-- because the savepoint rollback writes to
-- the log. Just return to the caller, which
-- should roll back the outer transaction.
-- After the appropriate rollback, echo error
-- information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
GO
But why do you need to test if XACT_STATE() <> -1 if any error within TRY..CATCH always puts your transaction in a uncommittable state? The example makes no sense to me.
Peter
February 3, 2010 at 5:56 am
February 3, 2010 at 6:27 am
As you have not raised an error or returned within the CATCH block I think the code will continue to execute.
(Edit: This will depend on what XACT_ABORT is set to.)
Try something like:
DECLARE @STR NVARCHAR(MAX)
DECLARE @amount DECIMAL(18,2)
BEGIN TRY
BEGIN TRANSACTION
SET @STR = '13,00'
SET @amount = CAST(@str AS DECIMAL(18,2))
INSERT INTO tbl__temp(a)
VALUES(@amount)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR('Problem with Insert', 16, 1)
END CATCH
I suspect it would be more efficient to check for valid datatypes in C#.
February 3, 2010 at 6:36 am
Peter Brinkhaus
I tried a new approach, where I started a nested transaction to solve the problem but since SQL Server does not support rollback of inner transactions without rolling back the outer transactions, it does not work either.
Peter for a very, very simple example of rolling back a nested transaction, using the save_point read this
http://www.sqlservercentral.com/questions/transactions/68308/ .
hope this will assist in clearing some of the confusion with transactions
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply