There has to be a better way to do this – if you know of one, kindly post a
comment.
Until then, here is a technique that will allow you to ensure that if a stored procedure
that BizTalk is calling is unable to complete its work successfully, you can rollback
the work and be notified of that fact within your orchestration.
-
In the stored procedure, use RAISERROR with a severity greater than 10 in the event
of an error. This should abort the (DTC) transaction that the BizTalk Sql
Adapter initiated (that wraps the work within the stored procedure.) -
Use a scope around the Send/Recieve shapes in your orchestration that deal with this
stored procedure. You will want to catch Microsoft.XLANGs.Core.XlangSoapException…but
you can’t. The designer won’t let you. So…be brave,
open the ODX, and look for the catch block so you can modify it as shown below (see
“ExceptionType”): -
With this solution, you will wind up with the message you are sending to the stored
procedure in a suspended state whenever the RAISEERROR occurs. This isn’t a
huge deal, but you have to plan how these will get cleaned up. It could be done
manually, with a script, or with an orchestration or send port that subscribes
to these suspended messages (try a filter with ErrorReport.FailureCode ==
0xc0c0167a to get started, though you probably want to be more specific.)
<om:Element Type="Catch" OID="e7590870…" ParentLink="Scope_Catch" …>
<om:Property Name="ExceptionName" Value="soapex" />
<om:Property Name="ExceptionType" Value="Microsoft.XLANGs.Core.XlangSoapException" />
<om:Property Name="IsFaultMessage" Value="False" />
<om:Property Name="ReportToAnalyst" Value="True" />
<om:Property Name="Name" Value="CatchException_2" />
<om:Property Name="Signal" Value="True" />
</om:Element>
>
After you’ve gone to these lengths, it is always worth considering whether you
want to have your orchestration call a component so you are a little closer to the
action. But, if you have need of the adapter, this should work out.