SQL Server 2012 make HTTP 'GET' Request fr

问题:

I was given a web service that returns a JSON object. My task is to make an HTTP 'GET' request to that web service and store the JSON data retrieved to a table every 5 minutes.

I am thinking about creating a stored procedure and then a job that would execute the stored procedure every 5 minutes.

My question is, can you make an HTTP request from a stored procedure? Is there a better approach to accomplish this goal?

回答1:

I ended up using a CLR function (using C#) to pull the JSON Object from the existing web service, convert it the JSON into XML (since MSSQML2012 does not support JSON) and return the xml data.

I built the CLR function(with .dll extension) and Created an assembly on MSSQML2012. Once the Assembly was there I created a MSSQML2012 function to execute the assembly file.

From there I created a stored procedure that Called the MSSQML2012 function, once the xml data was available in the store procedure, I just parsed through the nodes and inserted them into their respective tables.

My next step is to create a job that would execute the stored procedure as often as I want to.

Hope it helps!! :)

回答2:

Yes, this can be done via SQLCLR (i.e. "CLR Integration") in either a Stored Procedure or a Function (either scalar UDF or TVF). However, rather than just proceeding as if one were coding a regular Windows or Web app, there are various nuances to working within SQL Server CLR host that one should be aware of. I have documented much of this in the following S.O. answers:

• Call web service from SQL CLR? (other answers are linked in this one as well)
• Security error when calling external web service in visual studio

Also, please see the article I wrote regarding various nuances of working with SQLCLR: Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server) (free registration is required for that site, but there are other articles in this series that should be helpful :-).