HTTP POST using MSXML2.ServerXMLHttp.3.0 in stored procedure

microsoft.public.sqlserver.programming

1 Sep 2005 10:53 AM

Mark Wiewel

Hi,

I am trying to launch a HTTP-POST-request (not GET) from inside a
stored procedure using MSXML2.ServerXMLHttp.3.0.

The URL ist called without any problems, except the POST data isn't
transmitted.

I am not quite sure what the correct T-SQL syntax is, but I don't
receive any errors, so I guess, I am using the right one.

Find my sp attached below.

Any help is appreciated!

Cheers, Mark

CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000)
out)
As

Declare
@obj   int
,@hr   int
,@status int
,@msg varchar(255)

   exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
   if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end

   exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
   if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

   exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
   if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed'
goto eh end

   exec @hr = sp_OAMethod @obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
   if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

   exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
   if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end

   if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end

   exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
   if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end

   exec @hr = sp_OADestroy @obj
   return

eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
GO