I told you in my last blog post that an error never comes alone … when an error appears, it always appears in pairs. Or that, or I have a tendency to attract, or find weird errors! This time I was completely stunned: StoredProcedure does not exist, while trying to invoke a stored procedure through BizTalk WCF-SQL adapter:
Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[InsertTransaction] 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)
or in the event viewer:
A message sent to adapter “WCF-Custom” on send port “STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//AsyncTransactions?InboundId=ins” is suspended.
Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[InsertTransaction] 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: {71CDC883-AE8D-4303-A72E-ADA9ECE91981}
InstanceID: {1EA7B77C-924F-4C74-87DE-BCC4D1AD8E6A}
As I normally say, doubt what you read – even if it is on my blog – because there are many conspiracy theories, and we can find so-called solutions by googling it… But a lot of them are just that: conspiracy theories.
📝 One-Minute Brief
A troubleshooting guide that explains why the BizTalk WCF‑SQL adapter throws a MetadataException when a referenced stored procedure cannot be found in SQL Server, and how to identify and fix schema, naming, or deployment issues.
Cause
Personally, I usually like to start with the simplest thing: If it says that doesn’t exist… let’s check, and in this case, the error raised by the adapter is just stupid because the stored procedure exists in that specific database, as you may see in the picture below:
At first glance, it seems that was not a security issue because I was able to connect to the database; otherwise, I would have gotten the following error:
Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database “AsyncTransactions” requested by the login. The login failed.
Again, some conspiracy theories may let you think that:
- You should regenerate schemas.
- Or that may be a namespace mismatch.
- The operation is not properly set.
- The “?” character that you normally find in the URI is causing this problem.
- And my favorite is that you should grant “sysadmin” rights to the service account running the host instance.
Lucky, I remembered to double-check the security permission directly on SQL Server, so I opened the SQL Server Management Console with the service account and tried to execute the stored procedure and… guess what… I didn’t have permissions!
When I double-check the permissions to that service account, it had: db_datareader and db_datawriter… that in some situations are enough:
Of course, having sysadmin or db_owner would solve all my problems, but sometimes you are not allowed to do that for security reasons, and it is also not best practice, especially now with GDPR.
So the problem was clear: the service account that is running the host instance, which binds to that send port, didn’t have the right permissions to execute the stored procedure… unfortunately, the error raised by the adapter is out of context.
Solution
The best approach for you to solve this problem is to create a new server role for that particular database in SQL Server:
- In Object Explorer, access to your database and expand it.
- Expand the Security folder.
- Right-click the Database Roles folder and select New Database Role…
- In the New Database Role window:
- On the Role name property, on the General page, enter a name for the new database role, for example, db_spexecution.
- On the Owner property, inherit from db_datareader and db_datawriter (optional)
- On the Securables page, under Securables, click the Search button.
- On the Add Objects window, select Specific objects… and click OK.
- On the Select Objects window, click Object Types… and then select Stored Procedures.
- After selecting the object type, click Browse… and from the Browser for Objects window select the stored procedures you want to invoke. (only the one that you need)
- Click Ok and again OK to return to the main New Database Role window.
- And finally, on the General tab, add the service account that is running the host instance to the Role Members for that role.
- Click OK to finish.
You can now try to resend the suspended messages or send new ones, and you will be able to communicate and execute the stored procedures without any problem.
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.
Its very similar error I have got after store procedure changes. my BizTalk application send port is throwing the below error:
Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[xxxxxxxxxxxxx] 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)
After applying the above solution, I’ve still got the same results. I worked with my DBA and followed same as stated here in the blog. created a new server role and provided access to service account specially only for this stored procedure.