SQL Server 2012从存储过程发出HTTP'GET'请求 [英] SQL Server 2012 make HTTP 'GET' Request from a stored procedure

问题描述

为我提供了一个返回JSON对象的Web服务.我的任务是向该Web服务发出HTTP"GET"请求，并每5分钟将检索到的JSON数据存储到一个表中.

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.

我正在考虑创建一个存储过程，然后创建一个每5分钟执行一次存储过程的工作.

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

我的问题是，您可以从存储过程发出HTTP请求吗? 有没有更好的方法可以实现这一目标?

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

推荐答案

我最终使用CLR函数(使用C#)从现有Web服务中提取JSON对象，将其转换为XML(因为MSSQML2012不会)支持JSON)并返回xml数据.

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.

我构建了CLR函数(具有.dll扩展名)，并在MSSQML2012上创建了一个程序集.程序集在那里之后，我创建了一个MSSQML2012函数来执行程序集文件.

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.

从那里我创建了一个称为MSSQML2012函数的存储过程，一旦存储过程中有xml数据可用，我就通过节点进行解析并将它们插入到各自的表中.

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.

希望有帮助！！ :)

这篇关于SQL Server 2012从存储过程发出HTTP'GET'请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！