Making WCF-SQL Send Port work with Stored Procedure
[Disclaimer: I’m learning BizTalk the hard way – without a training course and with an impending deadline. These are my learnings, notes and reminders. If you find them useful, hurrah. If not, I doubt I can help you. This might not be the best way of doing things, so if you know your Biztalk, please feel free to correct me in comments.]
I had an issue with getting stuff into a database via a stored procedure. I’d created a schema using the Add > Add Generated Items > Consume Adapter Service route, and selected sqlBinding. From there I connected to my database, and chose my Insert stored procedure.
This created two schemas, one for the dataset definition. Everything looked good. I created a map to transpose data between the incoming schema and the newly created SQL schema.
When I deployed it, however, I had some issues. What should I use for the send port for this SQL data. Something would have to take this schema definition which VS had created for me and do something with it.
Initially I was confused because BizTalk out the box doesn’t include a WCF-SQL Adapter, otherwise I would have chosen that from the list, and avoided trying them all out. If WCF-SQL is not on your Adapter list, see MSDN-Adding the SQL Adapter to BizTalk Server Administration Console.
Setting up the send port seemed OK, there’s a useful Configure… button for building the Endpoint Address. However, when I tried (optimistically) I got this:
The adapter failed to transmit message going to send port SQLSendPort with URL “mssql://sageserver/sageserver/PS_Analytics”. It will be retransmitted after the retry interval specified for this Send Port. Details: “System.ArgumentNullException: Value cannot be null. Parameter name: key at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.CreateChannelFactory[TChannel](IBaseMessage bizTalkMessage) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.InitializeValues(IBaseMessage message) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2..ctor(IBaseMessage message, WcfTransmitter`2 transmitter) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfTransmitter`2.GetClientFromCache(String spid, IBaseMessage message) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfAsyncBatch`2.BatchWorker(List`1 messages).”
That’s not a helpful message. It doesn’t really tell you where to start looking, it doesn’t hint as to which particular key value is null and why it can’t be null.
For me, the problem was that I didn’t specific the SOAP Action Header. Seems obvious when you say it, but the send port didn’t have any real information about what it should do once it had connected to the database. Yes, that information IS in the schema, but I think Microsoft have been trying hard to abstract each layer completely, so it’s nessecary to define it here as well.
Elsewhere, on the Internet, I found that the format for the action should be (for a stored procedure) something like this: TypedProcedure/dbo/SP_NAME. This is worth a try, but it didn’t work for me. I got this:
The adapter failed to transmit message going to send port “SQLSendPort” with URL “mssql://SERVER_NAME/INSTANCE_NAME/DB_NAME”;. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name “SP_NAME” and namespace “http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo” was unexpected. Please ensure that your input XML conforms to the schema for the operation. 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)
Looking back at the schema which was automatically generated, the name of the /element/annotation/documentation/doc:Action value was actually Procedure/dbo/SP_NAME. So I changed the value of the action in the send port to: Procedure/dbo/SP_NAME
This also generated an error, but at least it was an error thrown back from the database due to one of the parameters not being passed. Essentially, the send port had done its job, and once I fixed the stored procedure, finally, it was working!
Adding the SQL Adapter to BizTalk Server Administration Console
Written by Tom Morgan
Tom is a Microsoft Teams Platform developer and Microsoft MVP who has been blogging for over a decade. Find out more.
Hi, sorry to reply to such an ancient post, but I have a similar problem but my issue is I am not using the DBO owner. My procedure would have the action Procedure/XYZ/SP_Name but when I do that, I get an error in the schema. Do I need to update the schema with this XYZ as the owner instead of DBO in all schemas? Thank you for your help.