"Error saving map. Stored procedure returned non-zero result." error message when you deploy the BizTalk Server 2009 applications in BizTalk Server 2009 Administration Console

A KB article related to this issue in BizTalk 2010 can be found here: http://support.microsoft.com/kb/2673264

However, somewhere between BizTalk 2009's CU3 and CU6 release this same issue was introduced to BizTalk 2009.  At the time of writing, there is no known fix from Microsoft but what you can do is temporarily rollback the BizTalkMgmt.dbo.dpl_SaveMap sproc so you can continue importing applications.

To rollback the sproc to CU3 state remove the highlighted code block below that has been introduced by a CU:

ALTER PROCEDURE [dbo].[dpl_SaveMap]
(
    @ArtifactId int,
    @AssemblyId int,
    @IndocDocSpecName nvarchar (256) ,
    @OutdocDocSpecName nvarchar (256) ,
    @ArtifactXml ntext
)
AS
    if not (exists(select * from bt_DocumentSpec where docspec_name = @IndocDocSpecName) and
        exists(select * from bt_DocumentSpec where docspec_name = @OutdocDocSpecName))
        return -1 --Fail if in and out schemas of a map are not present

    DECLARE @shareid  uniqueidentifier
    SELECT @shareid = newid()
    INSERT INTO bt_XMLShare( id,target_namespace, active, content )
        VALUES( @shareid, N'', 1, @ArtifactXml )
    INSERT INTO bt_MapSpec(
        itemid,
        assemblyid,
        shareid,
        indoc_docspec_name,
        outdoc_docspec_name
    )
    VALUES( @ArtifactId, @AssemblyId, @shareid, @IndocDocSpecName, @OutdocDocSpecName )
    RETURN 0
GO

WCF SQL Adapter Composite Operation Timeout

Microsoft.ServiceModel.Channels.Common.InvalidUriException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This error occurs when using a WCF SQL adapter composite operation with more operations than maxConnectionPoolSize where each operation returns a record set. This is independent of whether useAmbientTransaction is enabled or not. The result set could be as simple as @@ROWCOUNT.

It seems there may be an IDataReader created for each result set. Each result stream retains an open connection until all the requests have been executed. If there are more requests than there are connections available in the connection pool the process will exhaust all available connections in the pool and subsequently fall over.

To fix the issue, redesign the solution or remove the result set from the equation.

[Update] A colleague of mine reworked his sproc to use output parameters to allow a simple response to be returned and avoided this timeout issue.