Can SQL Begin Try/Catch be lying to me (in the profiler)?

Question

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.

Answer 1

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.