Today while importing/generating a WCF-SQL schema for a new BizTalk Server solution, we got the following error inside Visual Studio:
Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at ‘TypedPolling’
This is quite a generic error, and for you to have more insight, you need to click on Details.
Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at ‘TypedPolling’ —> System.Data.SqlClient.SqlException: Could not find stored procedure ‘dbo.<stored-procedure-name>’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Cause
As the description states, the generator tool was not able to find the stored procedure in the SQL database.
Because this was an inbound operation (polling), the first task I performed was to validate the existence of the stored procedure and determine whether there was a spelling error in the configuration of the polling statement. No spelling error was observed, and the stored procedure existed in the database.
The second thing you should perform is to Disconnect and make sure that the URI configuration is correct with regard to the SQL Server and instance name as well as the database name.
In my case, I forgot to specify the database name, which means the InitialCatalog property.
Because we configure the Server and InstanceName properties, we are able to connect the server, but when the tool tries to generate the Schemas, it doesn’t know where to connect.
Solution
Solving this issue is quite simple:
- You need to make sure that you configure properly the URI properties:
- Server property, with the name or network address of the host machine on which SQL Server resides.
- InstanceName property, with the instance name of SQL Server to connect to. If you need to connect to the default instance, you need to leave this property empty.
- InitailCatalog property, with the name of the database to which to connect.
After doing that, I was able to successfully generate the SQL schemas
Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
