Send XML HTTP Request by sp_OAMethod is not working in SQL 2008

_{Monday, June 28, 2010 10:53 PM}

Hello,

I have stored procedure to send XML HTTP request in SQL 2005 is working, but not in SQL 2008, will get error "sp_OAMethod SEND failed"

the Stored Procedure is below, please help!

ALTER PROCEDURE  [dbo].[HTTP_REQUEST]
(
@URI varchar(200),
@requestBody VARCHAR(MAX),
@response varchar(8000) OUT
)
AS

DECLARE @xhr INT,@result INT,@httpStatus INT,@msg VARCHAR(255)

EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP', @xhr OUT

IF @result <> 0 BEGIN RAISERROR('sp_OACreate on MSXML2.XMLHttp.5.0 failed', 16,1) RETURN
END

EXEC @result = sp_OAMethod @xhr, 'open', NULL, 'POST', @URI, false
IF @result <>0 BEGIN RAISERROR('sp_OAMethod Open failed', 16,1) RETURN
END

EXEC @result = sp_OAMethod @xhr, 'Send', NULL, @requestBody
IF @result <>0 BEGIN RAISERROR('sp_OAMethod SEND failed', 16,1) RETURN
END

EXEC @result = sp_OAGetProperty @xhr, 'status', @httpStatus OUT
print 'Status: ' +convert(varchar(10),@httpStatus)
IF @result <>0
    BEGIN RAISERROR('sp_OAMethod read status failed', 16,1) RETURN
    END

IF @httpStatus <> 200 BEGIN RAISERROR('sp_OAMethod http status bad', 16,1) RETURN
END

EXEC @result = sp_OAGetProperty @xhr, 'responseText', @response OUT
IF @result <>0 BEGIN RAISERROR('sp_OAMethod read response failed', 16,1) RETURN
END

EXEC @result = sp_OADestroy @xhr

RETURN

_{Thursday, July 1, 2010 2:20 PM}

Hi John,

Which version and edition of SQL Server are you using? I executed your stored procedure successfully on my machine.

Please refer to the following link which should help you:

http://www.mssqlcity.com/Articles/General/OleAutSP.htm

Thanks,

Ai-Hua Qiu

Constant dropping wears away a stone.

_{Thursday, July 1, 2010 7:55 PM}

Ai-Hua,

thank you for reply, I am using SQL 2008 enterprise (10.0.2531.0),

Yes, the store procedure is working on SQL 2005, but it is not working on SQL 2008

john