How can I make an HTTP request from a SQL server?

Question

I wanted to send an HTTP request from SQL server to Tomcat server. I installed SQL Express 2012 Express and not .NET on a Tomcat server. I went through this how to make an HTTP request from a SQL server

As stated in this article, "The WinHttp.WinHttpRequest.5.1 COM object must be installed on the server, some typical options are WinHttp.WinHttpRequest.5." I downloaded winhttp.zip from the winhttp download link , found winhttp.dll in the zip folder and pasted it into C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\Binn , as suggested in this msdn link .

Following the same advice, I ran the following line in SSMS:

 sp_addextendedproc 'GetHttp', 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2\MSSQL\Binn\winhttp.dll'; 

I also executed the following code in SSMS, as stated in "Make an HTTP Request from a SQL Server Link":

 Alter function GetHttp ( @url varchar(8000) ) returns varchar(8000) as BEGIN DECLARE @win int DECLARE @hr int DECLARE @text varchar(8000) EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @win EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false' IF @hr <> 0 EXEC sp_OAGetErrorInfo @win EXEC @hr=sp_OAMethod @win,'Send' IF @hr <> 0 EXEC sp_OAGetErrorInfo @win EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @win EXEC @hr=sp_OADestroy @win IF @hr <> 0 EXEC sp_OAGetErrorInfo @win RETURN @text END 

Then i get an error

Msg 2010, Level 16, State 1, GetHttp Procedure, Line 2
Cannot make a change to 'GetHttp' because it is an incompatible object type.

I do not know how to call the function of sending an HTTP request. I assume this is something like this GetHttp('http://www.google.co.in/') .

What am I missing?

Answer 1

I got a response from powershell. What I did was open powershell in sql server, then I executed the following code in powershell.

 $http_Request= New-Object system.Net.WebClient; $Result = $http_Request.downloadString("url") 

Answer 2

I got another answer. I created the procedure as follows

 CREATE procedure HTTP_Request( @sUrl varchar(200)) As Declare @obj int ,@hr int ,@msg varchar(255) exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0 failed', 16,1) return end exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end exec @hr = sp_OAMethod @obj, send, NULL, '' if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end exec @hr = sp_OADestroy @obj return eh: exec @hr = sp_OADestroy @obj Raiserror(@msg, 16, 1) return GO 

I called a stored procedure with url

 USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[HTTP_Request] @sUrl = N'url' SELECT 'Return Value' = @return_value GO 

Thanks guys for making me work.

Answer 3

It really helped me @niren.

I think that I will publish my amendment, which places it in a scalar function and allows you to get a response from the service. The only drawback is scalar functions that raisers cannot use, so there is something to think about catching elsewhere.

 CREATE function [dbo].[fn_HttpPOST] ( @sUrl varchar(8000) ) returns varchar(8000) as BEGIN DECLARE @obj int DECLARE @hr int DECLARE @msg varchar(8000) exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT if @hr <> 0 begin set @Msg = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' return @Msg end exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end exec @hr = sp_OAMethod @obj, send, NULL, '' if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end EXEC @hr=sp_OAGetProperty @Obj,'ResponseText',@msg OUTPUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj exec @hr = sp_OADestroy @obj RETURN @msg eh: exec @hr = sp_OADestroy @obj return @msg END 

Answer 4

Made this monster for my own needs.

 CREATE PROCEDURE [dbo].[RequestHttpWebService] @Url varchar(1024), @HttpMethod varchar(10), @ParamsValues varchar(1024), -- param1=value&param2=value @SoapAction varchar(1024) = null AS BEGIN SET NOCOUNT ON; if @HttpMethod in ('get','GET') and len(@ParamsValues) > 0 begin set @Url = @Url + '?' + @ParamsValues end declare @obj int ,@response varchar(8000) ,@responseXml xml ,@status varchar(50) ,@statusText varchar(1024) ,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end) exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj out exec sp_OAMethod @obj, 'Open', null, @method, @Url, false if @HttpMethod in ('get','GET') begin exec sp_OAMethod @obj, 'send' end else if @HttpMethod in ('post','POST') begin exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded' exec sp_OAMethod @obj, 'send', null, @ParamsValues end else if @HttpMethod in ('soap','SOAP') begin if @SoapAction is null raiserror('@SoapAction is null', 10, 1) declare @host varchar(1024) = @Url if @host like 'http://%' set @host = right(@host, len(@host) - 7) else if @host like 'https://%' set @host = right(@host, len(@host) - 8) if charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host) set @host = left(@host, charindex(':', @host) - 1) else set @host = left(@host, charindex('/', @host) - 1) declare @envelope varchar(8000) = '<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><{action} xmlns="http://tempuri.org/">{params}</{action}></soap:Body></soap:Envelope>' declare @params varchar(8000) = '' WHILE LEN(@ParamsValues) > 0 BEGIN declare @param varchar(256), @value varchar(256) IF charindex('&', @ParamsValues) > 0 BEGIN SET @param = left(@ParamsValues, charindex('&', @ParamsValues) - 1) set @value = RIGHT(@param, len(@param) - charindex('=', @param)) set @param = left(@param, charindex('=', @param) - 1) set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>' SET @ParamsValues = right(@ParamsValues, LEN(@ParamsValues) - LEN(@param + '=' + @value + '&')) END ELSE BEGIN set @value = RIGHT(@ParamsValues, len(@ParamsValues) - charindex('=', @ParamsValues)) set @param = left(@ParamsValues, charindex('=', @ParamsValues) - 1) set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>' SET @ParamsValues = NULL END END set @envelope = replace(@envelope, '{action}', @SoapAction) set @envelope = replace(@envelope, '{params}', @params) set @SoapAction = 'http://tempuri.org/' + @SoapAction print @host print @SoapAction print @envelope exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8' exec sp_OAMethod @obj, 'setRequestHeader', null, 'Host', @host exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction exec sp_OAMethod @obj, 'send', null, @envelope end exec sp_OAGetProperty @obj, 'responseText', @response out exec sp_OADestroy @obj select @status as [status], @statusText as [statusText], @response as [response] END GO 

Answer 5

The most flexible approach here is to use a custom CLR function, which you can simply define in C # as follows:

 [Microsoft.SqlServer.Server.SqlFunction] public static SqlString http(SqlString url) { var wc = new WebClient(); var html = wc.DownloadString(url.Value); return new SqlString (html); } 

Full installation instructions here - https://github.com/infiniteloopltd/SQLHttp

Answer 6

the correct way is to create a CLR that will contain C # code to send the http / s request, since we want to avoid memory leaks and security problems that might occur when using the old method like sp_OACreate.

for example: evar request = (HttpWebRequest)WebRequest.Create("http://www.example.com/recepticle.aspx"); var postData = "thing1=hello"; postData += "&thing2=world"; var data = Encoding.ASCII.GetBytes(postData); request.Method = "POST"; request.ContentType = "application/x-www-form-urlencoded"; request.ContentLength = data.Length; using (var stream = request.GetRequestStream()) { stream.Write(data, 0, data.Length); } var response = (HttpWebResponse)request.GetResponse(); var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd(); evar request = (HttpWebRequest)WebRequest.Create("http://www.example.com/recepticle.aspx"); var postData = "thing1=hello"; postData += "&thing2=world"; var data = Encoding.ASCII.GetBytes(postData); request.Method = "POST"; request.ContentType = "application/x-www-form-urlencoded"; request.ContentLength = data.Length; using (var stream = request.GetRequestStream()) { stream.Write(data, 0, data.Length); } var response = (HttpWebResponse)request.GetResponse(); var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();

using sp_OACreate is less secure, while the CLR can be signed and verified by you.