SQL Begin Try/Catch 会骗我吗(在分析器中)? [英] Can SQL Begin Try/Catch be lying to me (in the profiler)?

问题描述

我正在从 BizTalk 服务器调用存储过程并尝试调试它.

1)

根据我们在上面的分析器中看到的内容，我的 try/catch 是否按预期工作?

现在，当我从 SSMS 运行相同的存储过程时，诸如正常"之类的东西.

BizTalk 在 DTC 事务下运行所有​​内容.请注意，BEGIN TRY"和END TRY"语句出现在 SSMS 下的第二个配置文件中(并且BEGIN CATCH"没有触发 - 正如预期的那样)，并且它们没有出现在上面的第一个配置文件中(其中 sproc从 BizTalk 执行).

然后我在 SSMS 的测试脚本中添加了BEGIN DISTRIBUTED TRANSACTION"，结果与上面的其他 SSMS 测试相同.

注意:我注意到这个模式有一个更复杂的问题，并想简化它以便在这里发布.

解决方案

只是猜测，但可能是客户端(在本例中为 Biztalk)正在使用 SET FMTONLY ON提取结果集的形状(如果有).当此设置为 ON 时，所有分支都被执行"，包括 CATCH 块.您看到的结果正是您将在 SQL Profiler 中获得的结果.

I'm calling a stored proc from BizTalk server and trying to debug it.

1) Use SQL Debugger when stored proc called by an external process 2) Getting sp_tracegenerateevent to work in a stored procedure

I've been using SQL Profiler as my only tool to know what's going on. But now, I'm doubting if my try/catches are working correctly or not. Code works fine when running in SSMS, but when running from BizTalk sometimes seems like try/catch is catching all the time.

ALTER PROCEDURE WhatItsName 
    @CatchErrors varchar(max) OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF; 

DECLARE @debugMessage varchar(max) = '' 
DECLARE @RowCreateBy VARCHAR (100) 


SET @RowCreateBy = '108004'
BEGIN TRY 
    SET @RowCreateBy = '108005'
END TRY 
BEGIN CATCH 
    SET @debugMessage = 'set @RowCreatedBy Failed - how can this be ??? ' 
END CATCH 
etc... 

Results in Profiler:

Based on what we see in the profiler above, is my try/catch working as expected or not?

Now, when I run the same stored proc from SSMS, things like "normal".

BizTalk runs everything under a DTC transaction. Notice the "BEGIN TRY" and "END TRY" statements show up in the second profile under SSMS (and the "BEGIN CATCH" is NOT firing - as expected), and they don't show up in the first profile above (where sproc is executed from BizTalk).

I then added "BEGIN DISTRIBUTED TRANSACTION" to my test script in SSMS, and same result as the other SSMS test above.

NOTE: I noticed this pattern with a more complicated issue, and wanted to simplify it in order to post here.

解决方案

Just a guess, but probably the client (Biztalk in this case) is calling the procedure with SET FMTONLY ON to extract the shape of the result set, if any. When this setting in ON, all branches are 'executed', including CATCH blocks. The result you see is exactly what you'll get in SQL Profiler.

这篇关于SQL Begin Try/Catch 会骗我吗(在分析器中)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！