Handling SQL Errors in Try-Catch blocks using the WCF SQL Adaptor

At times  I am required to raise business rule type errors or T-SQL errors back to the BizTalk application from a stored procedure. This blog is about issues I encountered  when using a typed stored procedure schema.

The basic structure of the stored procedure is similar to below. The SQL statements are normally wrapped inside in a Try-Catch block with any business errors raised by the RAISERROR statement.

Create PROCEDURE [dbo].[MySproc]

@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID    
        
        --RAISERROR  ( 'This is my raised error',  16,1)        
        --select 1/0    
                
    
        
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

Below is the generated typed schema using the Consume Adaptor Service wizard. The first element StoredProcedureResultSet0 is the returned dataset and second element StoredProcedureResultSet1 is the dataset representing the error structure.

 

When dropping a test message in, the response message looks like this below:

As expected the StoredProcedureResultSet0 contains the recordset returned. Now if I modify the stored procedure to raise an error as highlighted below.

alter PROCEDURE [dbo].[MySproc]
@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID            
        RAISERROR  ( 'This is my raised error',  16,1)        
        --select 1/0    
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

The following message will be received:

Notice the StoredProcedureRecordSet0 has the recordset value returned from the select statement and  StoredProcedureRecordSet1 has the error message that was thrown from the RASIERROR statement. This is what you would expect.

Now look what happens if you cause a SQL error.  I generated an error by dividing 1 by zero as highlighted below which will be caught by the Begin-Catch statement block.

alter PROCEDURE [dbo].[MySproc]
@RequestID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  begin try

        select @RequestID            
        --RAISERROR  ( 'This is my raised error',  16,1)        
        select 1/0    
        return 0
    end try
    begin catch
        --error handler
        select     ERROR_NUMBER() AS ExceptionErrorNumber
                ,ERROR_MESSAGE() AS ExceptionErrorMessage;
            
        return 1    
    end catch;
END

The response message gets suspended with the following error:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_Custom” with URL “mssql://.//Prototype?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.AdapterException: The ResultSet returned as part of the Typed Stored Procedure or Typed Polling invocation did not match the metadata available. If this Stored Procedure or Polling Statement can return a variable number of result sets, consider using the un-typed Stored Procedure or un-typed Polling operation instead

The reason for the suspended message is when a SQL error is raised, the error recordset is placed in another StoredProcedureRecordSet array starting at element 2 as shown below:

From this point on any further requests that generate any SQL errors will now be placed in the StoredProcedureRecordSet2 as shown until the send port is unlisted and restarted.

 

To solve this issue there are two options available.  The first option is described below.

1. Modify the generated SQL WCF schema to include a second StoredProcedureResultSet2 as shown to capture the SQL errors.

 

2. Create a map with an in-line XSLT functiod to map any one of the error elements to the destination schema.

The XSLT script is shown here which uses wildcards in the path leading up to the exception errors.

<Error>
<Number><xsl:value-of select="/*/*/*/*[local-name()='ExceptionErrorNumber' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/CatchExceptions']"/></Number>
<Message><xsl:value-of select="/*/*/*/*[local-name()='ExceptionErrorMessage' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/CatchExceptions']"/></Message>
</Error>

 

The second option is to develop a custom pipeline component using the XML Translator Stream to rename the element names that are greater than 1 . I will continue this in part 2 of this blog.

Enjoy.