Today, while trying to test a solution that was supposed to invoke a SQL Server Stored Procedure with optional parameters via the BizTalk Server WCF-SQL adapter, I got the 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.
📝 One-Minute Brief
A troubleshooting guide that explains why the BizTalk WCF‑SQL adapter throws the error “Procedure or function expects parameter which was not supplied” and how to fix parameter mapping and message structure issues when calling SQL stored procedures.
Cause
The cause of this problem is, once again, quite obvious, and the error message clearly identifies the origin of the problem. The stored procedure is expecting a specific field or several fields that are not being passed, perhaps because they are marked in the schema as optional fields.
Solution
The simple problem is to pass all the parameters presented and required by the stored procedure. You should pass them:
- as NULL, if they are nillable;
- or as empty strings;
That said, my problem was not that simple because the parameter described in the error above needs to be optional. After I analyzed 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 them to 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!
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
