BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.

In the past, I wrote a blog post about a similar type of error: BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[StoredProcedureName] of type StoredProcedure does not exist. The difference for this new post is that instead of being the Stored Procedure name that is complaining it is now complaining about the Column name:

A message sent to adapter “WCF-Custom” on send port ” STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//Database?” is suspended.
Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[IdRecord] of type StoredProcedure does not exist

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)
MessageId: {0193EE6F-8DFF-4861-87FB-FC1C82ECF3AB}
InstanceID: {59E3F39A-BF24-4583-BEA9-78CED5B621F7}

Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[ColumnName] of type StoredProcedure does not exist.

However, despite this error and despite the fact that we were talking about the same server and same project of the previous issue, one thing I was sure about: they were not related at all.

Cause

At a first glimpse, it seems that the stored procedure does not include the column name: IdRecord, or that this field was not passed to the stored procedure at all. So, this was my first point to validate, however, quickly I realized that:

  • The stored procedure had that field;
  • And I was correctly passing that field to the stored procedure in the message;

Just to precaution, I double-check if that was not related to security permission, and it was not also. So I went back to my some conspiracy theories list that I described on my previous post:

  • You should regenerate schemas: no, never
  • or that may be a mismatch in the namespaces: I believe it could be a possibility, but no, since I was using the same scheme and it was working fine
  • that the “?” character that you normally find in the URI is causing this problem: also impossible in my case.
  • And my favorite is that you should give “sysadmin” rights to the service account that is running the host instance: never.

But the last one put me thinking: the operation is not properly set.

Because the last change I did was redesign the solution that was performing composite operations with the SQL Adapter, in my case I was sending multiple-rows to update in the same message.

And now I was doing a single operation.

And by doing that the WCF-SQL Adapter was throwing this strange behavior.

Solution

The solution was quite simple. We just have to change the Action CompositeOperation with the correct operation action mapping as show bellow as an example:

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="SQLStatusUpdateOp" Action="TypedProcedure/dbo/sp_BTS_updateTransaction" />
</BtsActionMapping>

After doing this change, everything started working perfectly again.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top