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.

Inextensible

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.

Rectifier

When BizTalk builds the schema, yes, that would be the case. But I don't think it couldn't do that at run time, because then the SQL business logic wouldn't work. – Inextensible

Checking - maybe it does call it twice, once with FmtOnly On and once with FmtOnly Off. I will retest and check: geekswithblogs.net/SERivas/archive/2010/09/15/… – Inextensible

I checked the profiler, and yes it is running FmtOnly=On. BizTalk is returning error: System.InvalidCastException: Specified cast is not valid. I was thinking this was from the run time, and I was trying to debug my code, but maybe the error is coming from a mismatch based on the first FmtOnly=ON call. I will check in BizTalk forum to confirm; then come back and check answer when verified. – Inextensible

Thanks. Indeed, now that I got past other errors, I can see in the profiler that BizTalk is calling Stored Proc twice, once with FmtOnly=On and a second time to really execute it with FmtOnly=Off – Inextensible