当字符串数据被截断时，如何回滚TSQL中的事务？ [英] How to rollback a transaction in TSQL when string data is truncated?

问题描述

目前，我有一个大的导入过程，我试图包装一个事务，所以如果有什么事情 - 我可以回滚。我遇到的问题是，当TSQL内部的反击时，它将不会回滚当发生以下SQL错误

Currently I have a large import process that I'm trying to wrap inside a transaction so if anything breaks - i could rollback. The issue I have is that when the TSQL inside the trans blows up, it won't rollback when the following SQL error occurs

Msg 8152, Level 16, State 14, Line 249
String or binary data would be truncated.
The statement has been terminated.

以下包装此导入TSQL

The below wraps this import TSQL

DECLARE @error INT
SELECT @error = 0
BEGIN TRANSACTION

--** begin import TSQL

--** end import TSQL

SELECT @error = @@error 
IF @error != 0 GOTO handle_error

COMMIT

handle_error: 
IF @error != 0 
BEGIN 
ROLLBACK 
END

推荐答案

如果您在SQL 2005上可以尝试：

If your on SQL 2005 you can try:

BEGIN TRANSACTION
BEGIN TRY
    --Run your Statements
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
        ROLLBACK TRANSACTION
        DECLARE @Msg NVARCHAR(MAX)  
        SELECT @Msg=ERROR_MESSAGE() 
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
END CATCH

这篇关于当字符串数据被截断时，如何回滚TSQL中的事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！