none
sqlserver2008都sp2了,一年前的bug还是没有出补丁,让人失望。。。 RRS feed

  • 问题

  • http://www.go4answers.com/Example/send-xml-http-request-sp_oamethod-not-12066.aspx
    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
    
    

    2011年6月21日 14:03

全部回复