I have the following function that reads JSON files and parses them. Sometimes these files are not correct and so I don't want to take them. I tried to handle the problem with a try catch
.
...
OPEN cursor_id;
FETCH NEXT FROM cursor_id INTO @ID,@Data;
BEGIN TRAN INNEN
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SAVE TRANSACTION INNENKLEIN
INSERT INTO EinzelObjekteGeparst WITH (TABLOCK HOLDLOCK)
(docid, [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12)
SELECT @ID , [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12 from dbo.JSONPathsAndValues(@Data)
COMMIT TRAN INNENKLEIN
END TRY
BEGIN CATCH
SELECT
@ID AS DocID,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
XACT_State() as XACT_State,
@@TRANCOUNT as TRANCOUNT
IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION INNENKLEIN
IF (XACT_STATE()) = 1 COMMIT TRANSACTION INNENKLEIN
PRINT '>> ERROR docid ' + cast(@ID as nvarchar) + ' Rollback'
END CATCH
END;
COMMIT TRANSACTION
The parsing fails with the following error
Msg 13609, Level 16, State 4, Line 15
JSON text is not properly formatted. Unexpected character '<' is found at position 0.
This is what I want to handle.
Currently I already know, that this error/insert not trigger XACT_State
being <> -1 and this cause the following error
Msg 3931, Level 16, State 1, Procedure dbo.ParseEinzelObjekte, Line 65
[Batch Start Line 18] The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I also tried double nested try catch
and explicitly generated errors to trigger XACT_State
. That did not work either.
I need a solution handle the json error but only skip (Rollback) the faulty entry.
3条答案
按热度按时间6jjcrrmo1#
COMMIT TRANSACTION
has an optional parametertransaction_name
, but it is ignored by the SQL Server Database Engine.Your code should have only one
COMMIT TRAN
after the whole loop. It is not possible to commit part of the transaction. You can only rollback part of the transaction. So, inside the loop you should be able to rollback to the savepoint.So, remove both calls to
COMMIT TRAN INNENKLEIN
in your code. And add another call toinside the loop right before the end. The way it is written now, it will process the first row from the cursor over and over again.
igetnqfo2#
For this stuff, i would remove all transaction code, create a @t-variable, do the inserts in a loop to the @t-variable, and just do one big insert to the real table at the end wrapped in a transaction if you really need it (i'm not sure why).
js5cn81o3#
As mentioned by others, you should just remove the transaction handling altogether.
If you want to insert all the JSON in one go, you can simply use one big
INSERT
. Just useISJSON
to check the JSON is valid before parsing.If you want to avoid blowing out your transaction log then you can continue using a cursor, but you should still remove all the transaction handling. If you have the whole thing in a transaction then it would come out to the same result as above anyway, so it makes no sense to use one in that case.
SAVE TRANSACTION
does not help with keeping the transaction log under control: the outer transaction is still held open.You can avoid the errors still by using
ISJSON
to check for incorrect JSON data.