I told you in my last blog post an error never comes alone … when an error appears, it always appears two or three. 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 thru 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.
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 it’s just stupid because the stored procedure exists in that specific database as you may see in the picture below:
At a first glimpse, it seems that was not a security issue because I was able to connect to the database, otherwise, I would get 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 mismatch in the namespaces;
- the operation is not properly set;
- that the “?” character that you normally find in the URI is causing this problem;
- and my favorite is that you should give “sysadmin” rights to the service account that is 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 try to execute the stored procedure and… guest 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, given “sysadmin” or “db_ower” would solve all my problems but sometimes you are not allowed to do that for security reasons and also is not a best practice, special now with GDPR.
So the problem was clear that the service account that is running the host instance bind 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 “Add Objects” window, select “Specific objects…” and click “OK”
- On “Select Objects” windows, click “Object Types…” and then select “Stored Procedures”
- After selecting the object type, click “Browse…” and from the “Browser for Objects” windows 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 give, 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.
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.