How to call a web service from a 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 columns indicate the city and state of the user. I want to pass this address as a parameter to the Google Geocoding API web service and get the latitude and longitude.

I have successfully used this geocoding api in C #. But now I want to use it in a stored procedure.

Can anyone suggest how to do this? Or please provide me any links?

Any help would be appreciated!

Thanks.

Answer 1

For something like this, you do not need the full implementation of a web service. You can use SQLCLR (SQL Server.NET Integration) to send a request to the URL, return the response in XML (unless you can find a JSON library that will work without installing UNSAFE), and then parse that answer.

Take a look at the following MSDN pages:

• HttpWebRequest
• HttpWebResponse
• Xmldocument
• To avoid the address:
  • If you are using SQL Server 2005, 2008 or 2008 R2, use Uri.EscapeDataString since it was available before .NET. Framework v4.5
  • If you are using SQL Server 2012, 2014 or later, you can use either Uri.EscapeDataString, or if the server has been upgraded to at least the .NET Framework v4.5, then you can use WebUtility.UrlEncode

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

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

Just send that with these two variables replaced by their respective values ​​via HttpWebRequest , then call HttpWebRequest.GetResponse , then call HttpWebResponse.GetResponseStream . And don't forget to call the Close and Dispose HttpWebResponse (or create an instance in the using block) !!

Additional notes:

• If you have not already done so, you will need to enable (once) CLR Integration at the server level: Enabling CLR Integration
• Do not make the easy path and set the database to TRUSTWORTHY ON . Just sign the assembly with a password, and then create an asymmetric key in the master database, pointing to your signed DLL, then create a login from this asymmetric key and, finally, grant UNSAFE ASSEMBLY permission to enter the system. Then you can set the assembly WITH PERMISSION_SET = EXTERNAL_ACCESS .
• Do not use SP_OA * procedures as protected user3469363 . These OLE Automation procedures are deprecated with SQL Server 2005 and will (hopefully) be removed someday (hopefully soon). They are also less efficient and less reliable than SQLCLR.

Answer 2

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

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