Can SQL Server send a web request?

Question

I have an SQL Server database and an intranet web application on a local network. The intranet web application creates records and sends them to the database. I have an Internet web application that I would like to send new records to using the SQL Server database on the local network.

I can't change/modify the intranet web application for various reasons so the only option I have is to create a trigger on the local SQL Server that will send new records to the Internet web application using some sort of http post request or url call.

The INTERNET web application is set up with a RESTful api that can receive new records via form post to a publicly accessible url (e.g. http://www.example.com/records/new).

Does anyone know if sending data (xml, json, plain variables in the url) via a url can be accomplished in SQL Server?

Thanks for any thoughts!

Answer 1

It is possible, but in the real world is a little bit more complicated than the naive approach you envision. Primarily, it is unacceptable to have a trigger wait for a HTTP request:

• For one, your application will crawl to a screeching halt, because triggers will block resources (primarily locks) waiting for a response from some far and away WWW service.
• Second, more subtle but far worse, is the issue of correctness in presence of rollbacks. If the transaction that issued to HTTP requests rolls back, there is no way to 'undo' the HTTP request.

The solution is to decouple the trigger from the HTTP request via a queue. The trigger enqueues the request into a local queue and commits, while a separate piece of processing dequeues these requests and issues the HTTP request. This solves both problems pointed out above. You can use ordinary tables for queues (see Using Tables as Queues) or you can use Service Broker, both work well.

Now on how to dequeue this requests and actually place the HTTP call, I strongly recommend using a dedicated process (ie. an application dedicated for this purpose). While it is possible to use SQLCLR, it is a very bad choice. SQL Server resources (specifically workers) are far to precious to waste on waiting for Internet responses.

Answer 2

As a good (rather reliable and scalable) option, you can use SQL CLR to interact with web application / web service.

For example, search for "SQL CLR WebRequest" or "SQL CLR WCF".

Answer 3

First do The follow

sp_configure 'show advanced options', 1;
go
RECONFIGURE;
go
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then Create a function

create 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

use like:

select dbo.GetHttp('http://127.0.0.1/mywebpage.php')