0

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?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
jorgeAChacon
  • 319
  • 2
  • 5
  • 21
  • I'm not sure which frameworks to write the code or the service in. I'm a web developer. You metioned vb.net,c# , but you can build many type of applications using this, would you be able to guide me a little more? @ArunprasanthKV – jorgeAChacon Jan 15 '16 at 17:31
  • Would it be a SQL Server Database Project (CLR)? – jorgeAChacon Jan 15 '16 at 17:38
  • I'm using C#. Ok, but if I create a web service that calls the other web service how can I set this up so that the web service is called every 5 minutes without anyone calling it? @ArunprasanthKV – jorgeAChacon Jan 15 '16 at 17:47
  • then you have to write a window service then you can run it in specific intervels, just like emailing services we are used in most of the web applications – Arunprasanth K V Jan 15 '16 at 18:04
  • You can call a web service from an SSIS package, a custom CLR function, executing a powershell command or according to the following URL, Microsoft.XMLHTTP : http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/validate-a-url-from-sql-server.aspx My suggestion would be the CLR function because you can use the features of .NET to help you parse out the JSON object, handle exceptions and do all that wonderful .NET stuff. – Robert Paulsen Jan 16 '16 at 05:24
  • @RobertPaulsen Thanks for your guidance, I believe the CLR function is going to take me in the right direction. I'll post an answer once I have everything wired up. – jorgeAChacon Jan 20 '16 at 17:36
  • @RobertPaulsen and jorge: I didn't see this question until just now, but I added an answer with some links to info on this topic that you should be aware of if implementing this type of code. – Solomon Rutzky Jan 22 '16 at 23:26

2 Answers2

0

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!! :)

jorgeAChacon
  • 319
  • 2
  • 5
  • 21
0

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:

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 :-).

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171