Quick HOW TO note
Sending an HTTP request from a SQL Server stored procedure

Keywords

SQL Server, HTTP, stored procedure, MSXML, XMLHTTP, OLE

by Faustino Dina Rivas

Published: May 9, 2002
Last Updated: May 9, 2002

I was in need of regularly sending an http request in batch to a maintenance procedure in a web site I'm developing using ASP and SQL Server.

I could do that using Windows Scripting Hosting for generating the request, and the Windows scheduler to schedule the batch job, but that way I needed to introduce more elements to the maintenance and administration factor. So I decided to do that from the SQL Server environment.

I didn't found any function to initiate an HTTP request within SQL Server, nor sample code to do that, so I decided to use the handy Microsoft.XMLHTTP component available within the much publicized MSXML component. It is the stored procedure I cooked. I hope you will find it useful.

For any comment or suggestion, contact me at faustino_dina@yahoo.com. 

If Exists (Select * From sysobjects Where id = object_id('sp_send_http') and sysstat & 0xf = 4)
Drop Procedure sp_send_http

Go

CREATE PROCEDURE sp_send_http (@sUrl varchar(200))
As
Declare
   @iReq int
  ,@hr int
  ,@sResponse varchar(1000)

   EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
   if @hr <> 0
      Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)

   EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'POST', @sUrl, true
   if @hr <> 0
      Raiserror('sp_OAMethod Open FAILED!', 16, 1)
   EXEC @hr = sp_OAMethod @iReq, 'send'
   if @hr <> 0
      Raiserror('sp_OAMethod Send FAILED!', 16, 1)
   EXEC @hr = sp_OAGetProperty @iReq, 'responseText', @sResponse OUT
1