SQL 2008 CLR and Bing Maps

Question

I've developed a small C# application that will let me geocode a table of address using Bing's geocode service configured as a serivce reference. I have the assembly loaded but when I attempt to call it via stored procedure I received the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Geocoder": 
System.InvalidOperationException: Could not find endpoint element with name 'BasicHttpBinding_IGeocodeService' and contract 'BingMapsGeo.IGeocodeService' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this name could be found in the client element.
System.InvalidOperationException: 
   at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName)
   at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)
   at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress)
   at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName)
   at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory()
   at System.ServiceModel.EndpointTrait`1.CreateChannelFactory()
   at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait)
   at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()
   at System.ServiceModel.ClientBase`1..ctor(String endpointConfigurationName)
   at Geocoder.BingMapsGeo.GeocodeServiceClient..ctor(String endpointConfigurationName)
   at Geocoder.UserDefinedFunctions.geocode(SqlString AddressLine, SqlString City, SqlString State, SqlString Zip)

I'm fairly certain the actual C# assembly is correct but I think I'm missing something with regards to the actual web service.

I've been searching for solutions everywhere but haven't found one that seems to work.

I'm running SQL 2008R2 on Server 2008R2 with the latest patches and .NET versions.

Any ideas would be most welcome.

Answer 1

Doing Web service calls from SQLCLR is possible, but is always a bad idea. SQL Server resources are far to precious to have them blocked waiting for responses from Internet. And ultimately there is zero advantage of doing it from SQLCLR, other than a (dubious) coolness factor. Also, loading WCF into SQLCLR is not supported, see Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment. And finally, SQLCLR assemblies cannot see any .config configuration sections. There is a know workaround by placing the configuration inside the sqlservr.exe.config, but is also unsupported.

Do the web service calls from the client (your app) and then update the database. This is not only supported, is actually the correct way.

Answer 2

While I agree with many of Remus' points, I disagree that only single "correct" way to call web services is in the application layer. Calling services directly from SQL Server can have its uses (although, I admit, this is rarely a good idea in a production environment). One of the benefits of SQLCLR is that, if you abstract your code into re-usable, modular units, you should be able to easily move functions between the database and other .NET-based layers of your application hierarchy, and the same geocoding function can be used in a client application, web-tier, or database tier with only a minimum amount of recording required.

If you are going to go down the SQLCLR route, however, you might want to consider using one of the other Bing Maps geocoding services (particularly the REST Locations API - http://msdn.microsoft.com/en-us/library/ff701715.aspx), which, since it is accessed via a simple REST URL, will avoid the problem with SOAP communication from SQL Server to WCF services. If you are geocoding a load of data, you might also want to consider the bulk geocoding capabilities provided by the Bing Spatial Data Services - http://msdn.microsoft.com/en-us/library/ff701734.aspx.

Either way, bear in mind that, a web service called to geocode address information inserted into a table as part of an INSERT/UPDATE trigger, for example, will not complete until a response is received, and will stall any further updates on that table. Let's say it takes a second to receive a response from the geocode service - if you've got any significant number of records being inserted into your address table you might quickly find your server being brought to its knees. You can alleviate this problem somewhat by employing Service Broker to manage requests to the geocode service asynchronously, and updating the table with the geooded response once returned.

Answer 3

Ok, after a bit more digging, guided by Remus and Alastair, I realized I was going about this the wrong way, since most of my goal was to simply geocode a single address given by a user in a Reporting Services report I really just needed to dig deeper into how to do it via native TSQL. I found a great article and sample code by David Rueter on SQLServerCentral and after a bit of modifying I was able to produce a UDF that not only uses Bing but also Google, Yahoo, and Mapquest and based on the quality of the response will switch between the engines to obtain the best answer. For batch encodes I'm working on a sproc that loads the data into a temp table and performs the geocoding and at the end updates the master table.

That solution seems to work for me, of course I was sure CLR was the way to go. Any other ideas I should be considering?

Thanks for all the help so far!