英文:
user cancellation not being caught by TRY CATCH
问题
我在 MSSMS 2012 遇到问题，我的 SQL 脚本可能被用户取消，我已经创建了一个 TRY CATCH 块，以记录到表中的任何错误事件（包括用户取消），但用户取消没有被记录（所以我认为它没有进入 CATCH 块）
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_WARNINGS OFF
BEGIN TRY
--用于记录事务的插入
BEGIN TRANSACTION
--用户取消的代码
--用于记录事务成功的更新
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE
@V_ERROR_NUM INT = ERROR_NUMBER(),
@V_ERROR_MSG VARCHAR(1000)=ERROR_MESSAGE(),
@V_ERROR_SEVERITY VARCHAR(100)=ERROR_SEVERITY(),
@V_ERROR_STATE VARCHAR(10)=ERROR_STATE()
--用于记录事务错误的更新
RAISERROR(@V_ERROR_MSG,@V_ERROR_SEVERITY,@V_ERROR_STATE);
END CATCH
SET ANSI_WARNINGS ON
TLDR：我创建的自定义 LOG 表中的更新永远不会在用户取消时执行。相反，错误号码为 "45188"，错误文本为 "The operation has been cancelled by user."，但这两列都为 NULL，即使 UPDATE 命令应该更改它们。
谢谢大家！
英文:
I'm having trouble in MSSMS 2012, my SQL SCRIPT may be cancelled by the user, I've made a TRY CATCH block to log into a table any error event (including user cancellation) but user cancellations are not being logged (so I don't think its getting into the CATCH block)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_WARNINGS OFF
BEGIN TRY
--INSERT FOR LOGGING THE TRANSACTION
BEGIN TRANSACTION
--CODE CANCELLED BY USER
--UPDATE FOR LOGGING THE TRANSACTION'S SUCCESS
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE
@V_ERROR_NUM INT = ERROR_NUMBER(),
@V_ERROR_MSG VARCHAR(1000)=ERROR_MESSAGE(),
@V_ERROR_SEVERITY VARCHAR(100)=ERROR_SEVERITY(),
@V_ERROR_STATE VARCHAR(10)=ERROR_STATE()
--UPDATE FOR LOGGING THE TRANSACTION'S ERROR
RAISERROR(@V_ERROR_MSG,@V_ERROR_SEVERITY,@V_ERROR_STATE);
END CATCH
SET ANSI_WARNINGS ON
TLDR: the Update into the custom LOG table I made is never executed on a user cancel.
Instead of getting "45188" as error number, and "The operation has been cancelled by user." as error text. Both columns have NULLs even though the UPDATE command should change them.
Thank you guys!
答案1
得分: 1
取消请求以ATTENTION TDS数据包的形式传递到服务器。这在用户代码中无法捕获，并将导致立即批处理终止。
如果您想记录此类错误以及其他严重的无法捕获的错误，请在客户端上记录它们。
为确保在这种情况下回滚正确发生，始终将XACT_ABORT设置为ON（正如您已经做的那样）。
不要使用RAISERROR，因为它无法正确重新引发现有错误。而是使用THROW;来重新引发错误（并确保您使用;终止前面的语句）。
英文:
A cancellation request comes through to the server as an ATTENTION TDS packet. This is not catchable in user code, and will result in immediate batch termination.
If you want to log such errors, as well as other severe uncatchable errors, log them on the client-side.
To ensure rollbacks happen correctly in such cases, always have XACT_ABORT ON (as you have done).
Do not use RAISERROR as it does not re-raise an existing error correctly. Instead use THROW; to rethrow an error (and make sure you terminate the previous statement with ;).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论