Network Programming in .NET

**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