There has to be a better way to do this – if you know of one, kindly post a

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.

  1. 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.)
  2. 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
  3. <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" />
  4. 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.)
  5. >

    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.