Recently one of my colleague asked me a question about the SQL Stored procedure execution in BizTalk Server 2010. His requirement was to ignore the SQL SP results including the returnStatus of the SP, however by default it does not happen. WCF SQL Adaptor expect a subscription to be in place for response of SP for a successful transaction, and incase of its absence sql adaptor transaction fails.
Cause
Why this happens? When you auto generate schemas for a stored procedure using Add Generated Items -> Consume Adaptor Service, the wizard create request response schema of the SPs as shown below.
and a binding file called as "WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml"
The binding generated by the wizard is always for two way send port. This is the reason it always expect to have a subscription of the response message.
Solution
The solution to above problem is simple with BizTalk Server 2010 compared to earlier version of it. You can modify the autogenerated binding as follows:
1) set IsTwoWay to "false"
2) comment out "receivepipeline" and "receivepipelinedata" section
Once binding is imported with these changes, send port will be created as one way and it will not expect to have a subscription of response message.
This solution is very well documented on microsoft web site http://msdn.microsoft.com/en-us/library/dd788154(v=bts.70).aspx.
