I wanted to send an HTTP request from SQL server to Tomcat server. I have installed SQL server 2012 express and non .NET application in Tomcat server. I have gone through this like Make a HTTP request from SQL server
As it says in the above article, “The COM object WinHttp.WinHttpRequest.5.1 must be installed on the server, some typical variations are WinHttp.WinHttpRequest.5”.
I have downloaded winhttp.zip from the winhttp download link, found winhttp.dll in the zip folder and pasted it in C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2MSSQLBinn as suggested in this msdn link.
Following that same advice, I have executed following line in SSMS:
sp_addextendedproc 'GetHttp',
'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2MSSQLBinnwinhttp.dll';
I also executed the following code in SSMS as said in “Make an HTTP request from 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 the error
Msg 2010, Level 16, State 1, Procedure GetHttp, Line 2
Cannot perform alter on ‘GetHttp’ because it is an incompatible object type.
I do not know how to call the function to send the HTTP request. I assume it is something like this GetHttp('http://www.google.co.in/').
What am I missing?
18
I got another answer as well. I created procedure like 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 the 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
Thank you guys to make me work this.
2
The most flexible approach here is to use a CLR User Defined Function, which you could 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 are here – https://github.com/infiniteloopltd/SQLHttp
1
That really helped me @niren.
Thought I’d post my amendment that puts it in scalar function and allows you to get the service response. Only downside is scalar funcs can’t raiserrors so there’s 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
I got answer by powershell. What I did is open powershell in sql server then I did execute following code in powershell.
$http_Request= New-Object system.Net.WebClient;
$Result = $http_Request.downloadString("url")
1
Made this monster for my own needs
CREATE PROCEDURE [dbo].[RequestHttpWebService]
@Url varchar(1024),
@HttpMethod varchar(10),
@ParamsValues varchar(1024), -- param1=value¶m2=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
2
the correct way is to buil a CLR, which will contain a C# code to send http/s request,
since we want to avoid memory leaks and security issues, which using old way like sp_OACreate can cause.
for example :
var request =
(HttpWebRequest)WebRequest.Create("http://www.example.com. /recepticle.aspx");
var postData = "thing1=hello";
postData += "&amp;amp;amp;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 CLR can be signed and check by you.
With these codes you can read Html Code from web pages through Httprequest or run SOAP web services.
Make HTTP request from SQLserver.
Send HTTP request via SQL Server.
Calling a SOAP webservice from SQL Server TSQL stored procedure.
Save Http Response To File.
DROP PROCEDURE IF EXISTS [dbo].[stp_HttpRequest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--1400/02/16-18:06
--Mahmood Khezrian
--Sp For Get Data (Page Html Or Webservice Result) From Web.
--Can Run Any Method (Get - Post , ....)
--Can Run Soap WebService
CREATE proc [dbo].[stp_HttpRequest]
@URI varchar(max) = N''
,@MethodName [sysname] = N'Post'
,@RequestBody nvarchar(max) = N'' --Neded For Run Soap Webservices , Soap XML Data Parameters
,@SoapAction varchar(500) --Neded For Run Soap Webservices
,@UserName [sysname] --(Optonal) DomainUserName or UserName
,@Password [sysname] --(Optonal)
,@ResponseText nvarchar(max) output --Return Responce Data With This Variable
,@ExportToFile nvarchar(max)=Null --If Pass This Parameter , After Run Request Save Responce Data (Returned Value) To File.
/*With ENCRYPTION*/ As
Set NoCount ON;
Declare @ErrorCode int
Set @ErrorCode = -1
/*Begin Transaction stp_HttpRequest*/
Begin Try
if (@MethodName = '')
RaisError (N'Method Name must be set.',16,1);
if (Patindex(N'%{RandomFileName}%',@ExportToFile)>0) Begin
Declare @FileName [sysname]
Set @FileName = Format(GetDate(),N'yyyyMMddHHmmss')+N'_'+Replace(NewID(),'-','')
Set @ExportToFile = Replace(@ExportToFile,N'{RandomFileName}',@FileName)
End--if
Set @RequestBody = REPLACE(@RequestBody,'&','&')
Set @ResponseText = N'FAILED'
Declare @objXmlHttp int
Declare @hResult int
Declare @source varchar(255), @desc varchar(255)
Set @ErrorCode = -100
Exec @hResult = sp_OACreate N'MSXML2.ServerXMLHTTP', @objXmlHttp OUT
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'Create failed MSXML2.ServerXMLHTTP',
MedthodName = @MethodName
RaisError (N'Create failed MSXML2.ServerXMLHTTP.',16,1);
End--if
Set @ErrorCode = -2
-- open the destination URI with Specified method
Exec @hResult = sp_OAMethod @objXmlHttp, N'open', Null, @MethodName, @URI, N'false', @UserName, @Password
if (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'Open failed',
MedthodName = @MethodName
RaisError (N'Open failed.',16,1);
End--if
Set @ErrorCode = -300
-- Set Timeout
--Exec @hResult = sp_OAMethod @objXmlHttp, N'setTimeouts', Null, 5000,5000,15000,30000
Exec @hResult = sp_OAMethod @objXmlHttp, N'setTimeouts', Null, 10000,10000,30000,60000
if (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'SetTimeouts failed',
MedthodName = @MethodName
RaisError (N'SetTimeouts failed.',16,1);
End--if
Set @ErrorCode = -400
-- set request headers
Exec @hResult = sp_OAMethod @objXmlHttp, N'setRequestHeader', Null, N'Content-Type', 'text/xml;charset=UTF-8'
if (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'SetRequestHeader failed',
MedthodName = @MethodName
RaisError (N'SetRequestHeader (Content-Type) failed.',16,1);
End--if
Set @ErrorCode = -500
-- set soap action
if (IsNull(@SoapAction,'')!='') Begin
Exec @hResult = sp_OAMethod @objXmlHttp, N'setRequestHeader', Null, N'SOAPAction', @SoapAction
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'SetRequestHeader (SOAPAction) failed',
MedthodName = @MethodName
RaisError (N'SetRequestHeader failed.',16,1);
End--if
End--if
Set @ErrorCode = -600
--Content-Length
Declare @len int
set @len = len(@RequestBody)
Exec @hResult = sp_OAMethod @objXmlHttp, N'setRequestHeader', Null, N'Content-Length', @len
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'SetRequestHeader (Content-Length) failed',
MedthodName = @MethodName
RaisError (N'SetRequestHeader failed.',16,1);
End--if
-- if you have headers in a Table called RequestHeader you can go through them with this
/*
Set @ErrorCode = -700
Declare @HeaderKey varchar(500), @HeaderValue varchar(500)
Declare RequestHeader CURSOR
LOCAL FAST_FORWARD
FOR
Select HeaderKey, HeaderValue
FROM RequestHeaders
WHERE Method = @MethodName
OPEN RequestHeader
FETCH NEXT FROM RequestHeader
INTO @HeaderKey, @HeaderValue
WHILE @@FETCH_STATUS = 0
BEGIN
Set @ErrorCode = -800
--Select @HeaderKey, @HeaderValue, @MethodName
Exec @hResult = sp_OAMethod @objXmlHttp, 'setRequestHeader', Null, @HeaderKey, @HeaderValue
IF @hResult <> 0
BEGIN
Set @ErrorCode = -900
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @MethodName
RaisError (N'SetRequestHeader failed.',16,1);
END
FETCH NEXT FROM RequestHeader
INTO @HeaderKey, @HeaderValue
END
CLOSE RequestHeader
DEALLOCATE RequestHeader
*/
Set @ErrorCode = -1000
-- send the request
Exec @hResult = sp_OAMethod @objXmlHttp, N'send', Null, @RequestBody
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'Send failed',
MedthodName = @MethodName
RaisError (N'Send failed.',16,1);
End--if
Declare @StatusText varchar(1000), @Status varchar(1000)
Set @ErrorCode = -1100
-- Get status text
Exec sp_OAGetProperty @objXmlHttp, N'StatusText', @StatusText out
Exec sp_OAGetProperty @objXmlHttp, N'Status', @Status out
Select @Status As Status, @StatusText As statusText, @MethodName As MethodName
-- Get response text
Declare @Json Table (Result ntext)
Declare @Xml xml
Set @ErrorCode = -1200
Insert @Json(Result)
Exec @hResult = dbo.sp_OAGetProperty @objXmlHttp, N'ResponseText'
Set @ErrorCode = -1300
--Exec @hResult = dbo.sp_OAGetProperty @objXmlHttp, N'ResponseText', @ResponseText out
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'ResponseText failed',
MedthodName = @MethodName
RaisError (N'ResponseText failed.',16,1);
END--if
Set @ErrorCode = -1400
--Set @ResponseText=Replicate(Convert(varchar(max),N'1'),1000000)
if (IsNull(@SoapAction,'')!='') Begin
Select @Xml=CAST(Replace(Replace(Replace(Cast(Result As nvarchar(max)),N'utf-8',N'utf-16'),N'.0,',N','),N'.00,',N',') As XML)
From @Json
Set @ErrorCode = -1500
Select @ResponseText = x.Rec.query(N'./*').value('.',N'nvarchar(max)')
From @Xml.nodes(N'.') as x(Rec)
End--if
Else Begin
Select @ResponseText= Result From @Json
End--Else
Set @ErrorCode = -1600
--Export To File
if (IsNull(@ExportToFile,'')!='') Begin
Declare @objToFile int
Declare @FileID int
Set @ErrorCode = -1700
--Create Object
Exec @hResult = sp_OACreate 'Scripting.FileSystemObject', @objToFile OUT
IF (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'Create failed Scripting.FileSystemObject',
MedthodName = @MethodName
RaisError (N'Create failed Scripting.FileSystemObject.',16,1);
End--if
Set @ErrorCode = -1800
--Create Or Open File
Exec @hResult = sp_OAMethod @objToFile, 'OpenTextFile' , @FileID OUT, @ExportToFile,2,1,-1
if (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'OpenTextFile failed',
MedthodName = @MethodName
RaisError (N'OpenTextFile failed.',16,1);
End--if
Set @ErrorCode = -1900
--Write Data To File
Exec @hResult = sp_OAMethod @FileID, 'Write', Null, @ResponseText
if (@hResult <> 0 ) Begin
Exec sp_OAGetErrorInfo @objXmlHttp, @source OUT, @desc OUT
Select hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = N'Write To File failed',
MedthodName = @MethodName
RaisError (N'Write To File failed.',16,1);
End--if
Set @ErrorCode = -2000
--Close File
Exec sp_OAMethod @FileID, 'Close'
--Delete Objects
Exec sp_OADestroy @FileID
Exec sp_OADestroy @objToFile
End--if
Set @ErrorCode = 0
/*If (@@TranCount > 0)
Commit Transaction stp_HttpRequest*/
End Try
Begin Catch
/*If (@@TranCount > 0)
Rollback Transaction stp_HttpRequest*/
Exec [dbo].[stp_GetErrorInfo]
End Catch
Exec sp_OADestroy @objXmlHttp
Return @ErrorCode
GO
--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//
--Example For Run Soap WebService
DECLARE @RC int
DECLARE @URI varchar(max)
DECLARE @MethodName sysname
DECLARE @RequestBody nvarchar(max)
DECLARE @SoapAction varchar(500)
DECLARE @UserName sysname=''
DECLARE @Password sysname=''
DECLARE @ResponseText nvarchar(max)
DECLARE @intA nvarchar(10)
DECLARE @intB nvarchar(10)
Set @intA = N'100'
Set @intB = N'200'
Set @URI = N'http://www.dneonline.com/calculator.asmx'
Set @MethodName = N'POST'
Set @RequestBody =
N'<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>
<Add xmlns="http://tempuri.org/"><intA>'+@intA+'</intA><intB>'+@intB+'</intB></Add>
</soap:Body>
</soap:Envelope>'
Set @SoapAction = N'http://tempuri.org/Add'
EXECUTE @RC = [dbo].[stp_HttpRequest]
@URI
,@MethodName
,@RequestBody
,@SoapAction
,@UserName
,@Password
,@ResponseText OUTPUT
Print @ResponseText
Print Len(@ResponseText)
Go
--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//--//
--Example For Feach Data From Website
DECLARE @RC int
DECLARE @URI varchar(max)
DECLARE @MethodName sysname
DECLARE @RequestBody nvarchar(max)
DECLARE @SoapAction varchar(500)
DECLARE @UserName sysname
DECLARE @Password sysname
DECLARE @ResponseText nvarchar(max)
Declare @ExportToFile nvarchar(max)
Set @URI = N'/questions/17407338/how-can-i-make-http-request-from-sql-server'
Set @MethodName = N'GET'
Set @RequestBody = N''
Set @SoapAction = N''
Set @ExportToFile = N'C:TempExport{RandomFileName}.html'
EXECUTE @RC = [dbo].[stp_HttpRequest]
@URI
,@MethodName
,@RequestBody
,@SoapAction
,@UserName
,@Password
,@ResponseText OUTPUT
,@ExportToFile
Print @ResponseText
Print Len(@ResponseText)
Go