Make a HTTP request from SQL server
**Update**
A better approach is listed here: https://blog.dotnetframework.org/2019/09/17/make-a-http-request-from-sqlserver-using-a-clr-udf/
Here is a UDF that allows HTTP GET requests from SQL server, for example
select dbo.GetHttp(‘http://fiachsapp.appspot.com/’)
A few “Gotcha’s” are;
* The HTML Content must be less than 8000 bytes, otherwise you get the error:
0x8004271A ODSOLE Extended Procedure Error in srv_convert.
* The COM object WinHttp.WinHttpRequest.5.1 must be installed on the server, some typical variations are WinHttp.WinHttpRequest.5
and WinHttp.WinHttpRequest. A search for the CLSID in Regedit should find the one you are using.
* You have to enable OLE Automation on the SQL server as follows;
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
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
Thanks it’s really very useful
I find that the noted Error in srv_convert happens if the length is > 4000. See: http://support.microsoft.com/kb/325492
It appears this old SQL 2000 issue, which was supposed resolved in a SQL 2000 SP, exist in SQL 2005 and SQL 2008, as of August 2011. 😦
Very helpful, thank you!
Thanx a lot!
I have added link to this post on my Polish SQL and C# blog
Good solution!
Thanks a ton…
Really appriciated. Excellent solution.
I’ve used this solution several times in the past…works great! Just ran into an issue with web servers that must be logged into. The login GetHttp works just fine…however any further requests appear to be denied as the web server doesn’t see me as being logged in still. Any idea?
TIA,
Jason
I am getting “The operation timed out ” 😦
me too…
i am required it in the trigger
YESSSS!!!!!!!!
Thank you very much. 🙂
9 years later, Works Like a charm. New learning curve for me towards Ole Automation Procedures. Thanks a lot man.