Home > Uncategorized > Make a HTTP request from SQL server

Make a HTTP request from SQL server

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

Advertisements
Categories: Uncategorized
  1. Urvish Suthar
    June 28, 2011 at 11:26 am

    Thanks it’s really very useful

  2. John Neighbors
    August 1, 2011 at 11:22 pm

    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. 😦

  3. September 28, 2011 at 9:20 pm

    Very helpful, thank you!

  4. March 2, 2012 at 7:34 pm

    Thanx a lot!
    I have added link to this post on my Polish SQL and C# blog
    Good solution!

  5. Ajit
    March 29, 2012 at 11:44 am

    Thanks a ton…

    Really appriciated. Excellent solution.

  6. Jason Leadbetter
    April 25, 2012 at 9:24 pm

    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

  7. March 15, 2015 at 9:26 am

    I am getting “The operation timed out ” 😦

  8. Kaya Smith
    April 28, 2015 at 5:44 pm

    YESSSS!!!!!!!!

  9. Cong Tran
    July 18, 2016 at 4:04 am

    Thank you very much. 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: