使用存储过程调用 页面，也可以是webservices

CREATE PROCEDURE HTTP_REQUEST (@URI VARCHAR (200)
                               , @response VARCHAR (8000) OUT)
AS
DECLARE @xhr INT, @result INT, @httpStatus INT, @msg VARCHAR (255)
---------------------------------------------------------------
--创建对象
---------------------------------------------------------------
EXEC @result = sp_OACreate 'MSXML2.XMLHTTP', @xhr OUT
IF @result <> 0
    BEGIN
        RAISERROR ('sp_OACreate on MSXML2.XMLHTTP failed', 16, 1)
        RETURN
    END
---------------------------------------------------------------
--open请求
---------------------------------------------------------------
EXEC @result = sp_OAMethod @xhr, 'open', NULL, 'GET', @URI, false
IF @result <> 0
    BEGIN
        RAISERROR ('sp_OAMethod Open failed', 16, 1)
        RETURN
 END
---------------------------------------------------------------
--send请求
---------------------------------------------------------------
EXEC @result = sp_OAMethod @xhr, SEND, NULL, ''

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

 

 

 

 

 

 

 

 

 

 

CREATE  PROC SendMailAgain_Period(@strBeginDate VARCHAR (10) ,@strEndData VARCHAR (10))
AS

DECLARE @url  VARCHAR(200);
DECLARE @ResponseText VARCHAR (8000);

SET @url = 'http://a4-hgh:82/scanerwebservice/PosSales.asmx/SendMailAgain?strBeginDate=' + @strBeginDate + '&strEndData=' + @strEndData

EXEC HTTP_REQUEST    @url   ,@ResponseText   OUTPUT

DECLARE @returnValue VARCHAR (10)
SET @returnValue = convert (xml, @ResponseText) .value ('.', 'varchar(10)');

 

IF @returnValue = 'true'
 print 1