How to invoke webservice from SQL Server stored procedure

Question

I would like to know how to use a web service from a SQL Server stored procedure. I have a table in which the columns specify the city and state of the user. I want to pass this address as a parameter to Google Geocoding API web service and get the latitude and longitude.

I have successfully made use of this geocoding api in c#. But now, I want to use it in a stored procedure.

Can any one please suggest how to get this done? Or please provide me any links?

Any help will be appreciated!!

Thanks.

Answer 1

For something like this, you don't need a full web service implementation. You can use SQLCLR (SQL Server's .NET Integration) to submit the request to the URL, get the response back in XML (unless you can find a JSON library that will work without being set to UNSAFE), and then parse that response.

Look at the following MSDN pages:

• HttpWebRequest
• HttpWebResponse
• XmlDocument
• To escape the address:
  • If you are using SQL Server 2005, 2008, or 2008 R2, then use Uri.EscapeDataString as it was available prior to .NET Framework v4.5
  • If you are using SQL Server 2012, 2014, or newer, then you can use either Uri.EscapeDataString or, if the server has been updated to at least .NET Framework v4.5, then you can alternatively use WebUtility.UrlEncode

According to the Google Geocoding API documentation, the API URI should be formatted similarly to the following:

https://maps.googleapis.com/maps/api/geocode/xml?address={EscapedAddress}&key={API_KEY}

Just submit that with those 2 variables substituted with their proper values via HttpWebRequest, then call HttpWebRequest.GetResponse, then call HttpWebResponse.GetResponseStream. And do not forget to call the Close and Dispose methods of HttpWebResponse (or instantiate it in a using block)!!

Additional notes:

• If not already done, you will have to enable (one time) "CLR Integration" at the server level: Enabling CLR Integration
• Do not take the easy road and set the database to TRUSTWORTHY ON. Just sign the assembly with a password, then create an asymmetric key in the master database by pointing to your signed DLL, then create a login from that asymmetric key, and finally grant that login the UNSAFE ASSEMBLY permission. Then you can set the assembly WITH PERMISSION_SET = EXTERNAL_ACCESS.
• Do not use the SP_OA* procedures as advocated by user3469363. Those OLE Automation procedures have been deprecated since SQL Server 2005 and will (hopefully) be removed someday (hopefully soon). They are also less efficient and less secure than SQLCLR.
• Even more notes can be found in my answer to a similar question on DBA.StackExchange: Bringing web service data into SQL server

Answer 2

Omar Frometa has an example on how to do this. go this link http://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur

In summary, use these extended stored procedures: SP_OACreate and SP_OAMethod to access objects such as MSXML2.ServerXMLHttp.