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 :
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)
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.
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.
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.