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.

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 “ExceptionType”):

<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>

3. 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.)

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.