BizTalk WCF-SQL Error: System.Data.SqlClient.SqlException (0x80131904): Procedure or function ” expects parameter ‘@<parameter name’, which was not supplied.

Today while trying to test a solution where it was supposed to invoke a SQL Server Stored Procedure with optional parameters thru the BizTalk Server WCF-SQL adapter I got this following error: Procedure or function expects parameter which was not supplied.

A message sent to adapter “WCF-Custom” on send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URI “mssql://.// AsyncTransactions?” is suspended.

Error details: System.Data.SqlClient.SqlException (0x80131904): Procedure or function ‘InsertTransaction’ expects parameter ‘@TransactionId’, which was not supplied.

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

ClientConnectionId:2731f081-b009-4ba5-a420-95efe7307e71

Error Number:201,State:4,Class:16

MessageId: {746C3B89-A7ED-4C12-8548-0FE174ACA2CB}

InstanceID: {FF06027F-BB96-498B-9515-1E2D55FEAC5A}

Followed by other similar warning messages:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URL “mssql://.// AsyncTransactions?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException (0x80131904): Procedure or function ‘InsertTransaction’ expects parameter ‘@TransactionId’, which was not supplied.

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URL “mssql://.// AsyncTransactions?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name “Request” and namespace “http://BizTalkTesting.SQLBulkOperations” was unexpected. Please ensure that your input XML conforms to the schema for the operation.

Cause

The cause of this problem is, once again, quite obvious and the error message present clearly identifies the origin of the problem. The stored procedure is expecting a certain field or several fields that are not being passed maybe because they are marked on the schema as optional fields.

Solution

Of course, the simple problem is to pass all the parameter presented and required by the stored procedure. You should pass them:

• as NULL, if they are nillable;
• or as empty strings;

Having said that, my problem was not that quite simply because the parameter described in the error above needs to be optional. After I analyze the stored procedure contract I realized that all of them are set as mandatory fields that need to be passed:

ALTER PROCEDURE [dbo].[InsertTransaction]
    -- Add the parameters for the stored procedure here
    @SourceSystem  VARCHAR(50),
    @DestinationSystem VARCHAR(50),
    @TransactionId uniqueidentifier,
    @MessageId uniqueidentifier,
    @DocumentType VARCHAR(50),
    @DocumentId VARCHAR(100),
    @BodyMessage ntext,
    @BodyType VARCHAR(5),
    @Priority int
AS
BEGIN
...

When I say that all of them are mandatory, of course, I can set it as NULL but in terms of BizTalk Server, I need to send all the fields in the XML message.

To make it actually optional, we need to change the contract of the schema to something like this:

ALTER PROCEDURE [dbo].[InsertTransaction]
    -- Add the parameters for the stored procedure here
    @SourceSystem  VARCHAR(50),
    @DestinationSystem VARCHAR(50),
    @TransactionId uniqueidentifier  = null,
    @MessageId uniqueidentifier  = null,
    @DocumentType VARCHAR(50),
    @DocumentId VARCHAR(100),
    @BodyMessage ntext,
    @BodyType VARCHAR(5),
    @Priority int
AS
BEGIN BEGIN
...

Once we modify the stored procedure to have default NULL values the problem will be solved. Now we don’t need to send these fields as NULL or blank, now we actually can omit them from the message – optional fields!