In this article i will explain how to call a Web Service from a Store Procedure with parameters.
For this article i will use Google Maps API Web Services for web service call.
The Store Procedure for calling Web Service is
The basic idea is to construct the Url, then use the MSXML2.XMLHTTP object to make the request and get the response. From there, we then cast the response into Xml, and pull out the values of interest via xquery.
Something that is also very important when using the sp_OAMethod Stored Procedure this awaiting the POST or GET method, default almost always send POST, but if we sent this method, we can not display the XML reading generated by our Web Service, so we must use the GET method.
Now when we execute the store procedure, it need a parameter @Location then it will call the Google Maps API Web Services and return an XML.
Since this Store Procedure relies on OLE Automation, you will need to enable this on your SQL box:
On the SQL Server box go to…
Start-> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
Click “Surface Area Configuration for Features”
Click the DB -> Database Engine
Select OLE Automation & Check the checkbox Enable OLE Automation…
Close the tool and then the sproc should work.
