SQL 2008 CLR 和 Bing 地图
我开发了一个小型 C# 应用程序，可以使用配置为服务引用的 Bing 地理编码服务对地址表进行地理编码。我已加载程序集，但当我尝试通过存储过程调用它时，我收到以下错误：
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)
我相当确定实际的 C# 程序集是正确的，但我认为我缺少一些与实际 Web 服务有关的内容。
我一直在到处寻找解决方案，但还没有找到一个似乎有效的解决方案。
我在带有最新补丁和.NET 版本的 Server 2008R2 上运行 SQL 2008R2。
任何想法都将受到欢迎。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
从 SQLCLR 进行 Web 服务调用是可能的，但始终不是一个好主意。 SQL Server 资源非常宝贵，不能让它们阻塞等待来自 Internet 的响应。最终，除了（可疑的）冷静因素之外，从 SQLCLR 执行此操作的优势为零。此外，不支持将 WCF 加载到 SQLCLR，请参阅 SQL Server CLR 中未经测试的 .NET Framework 程序集的支持策略 -托管环境。最后，SQLCLR 程序集看不到任何 .config 配置节。有一个已知的解决方法是将配置放在 sqlservr.exe.config 中，但也不支持。
从客户端（您的应用程序）执行 Web 服务调用，然后更新数据库。这不仅是支持的，而且实际上是正确的方式。
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.
虽然我同意 Remus 的许多观点，但我不同意调用 Web 服务的唯一“正确”方法是在应用程序层。直接从 SQL Server 调用服务可以有其用处（尽管我承认，这在生产环境中很少是一个好主意）。 SQLCLR 的好处之一是，如果您将代码抽象为可重用的模块化单元，您应该能够在数据库和应用程序层次结构的其他基于 .NET 的层之间轻松移动函数，以及相同的地理编码函数可以在客户端应用程序、Web 层或数据库层中使用，只需最少的记录量。
但是，如果您打算采用 SQLCLR 路线，则可能需要考虑使用其他 Bing 地图地理编码服务之一（特别是 REST 位置 API - http://msdn.microsoft.com/en-us/library/ff701715.aspx)，其中，由于它是通过简单的 REST URL 访问的，因此将避免从 SQL Server 到 WCF 服务的 SOAP 通信问题。如果您要对大量数据进行地理编码，您可能还需要考虑 Bing 空间数据服务提供的批量地理编码功能 - http://msdn.microsoft.com/en-us/library/ff701734.aspx。
无论哪种方式，请记住，例如，调用对作为 INSERT/UPDATE 触发器的一部分插入表中的地址信息进行地理编码的 Web 服务，在收到响应之前不会完成，并且将停止该表上的任何进一步更新。假设需要一秒钟才能收到来自地理编码服务的响应 - 如果您将大量记录插入地址表中，您可能很快就会发现您的服务器崩溃了。您可以通过使用 Service Broker 异步管理对地理编码服务的请求，并在返回后使用地理编码响应更新表，从而在一定程度上缓解此问题。
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.
好吧，在 Remus 和 Alastair 的指导下进行了更多的挖掘之后，我意识到我的做法是错误的，因为我的大部分目标是简单地对用户在我真正需要的 Reporting Services 报告中给出的单个地址进行地理编码更深入地了解如何通过本机 TSQL 来完成此操作。我在 SQLServerCentral 上找到了 David Rueter 写的一篇很棒的文章和示例代码，经过一番研究后修改我能够生成一个 UDF，它不仅使用 Bing，还使用 Google、Yahoo 和 Mapquest，并根据响应的质量在引擎之间切换以获得最佳答案。对于批量编码，我正在开发一个存储过程，它将数据加载到临时表中并执行地理编码，最后更新主表。
这个解决方案似乎对我有用，当然我确信 CLR 是正确的选择。我还应该考虑其他想法吗？
感谢迄今为止所有的帮助！
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!