SQL 调用 webservice

webservice 
需要配置为get 方式

sp_configure ‘show advanced options‘, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures‘, 1;
GO
RECONFIGURE;
GO
/********************************************/

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)
--WebService地址：以http开头，结尾带斜杠，例如‘http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/‘ 
set @UrlAddress = ‘http://127.0.0.1/Service/MessageService.asmx/‘

declare @FunName varchar(50)
--WebService中调用的方法名：例如‘getMobileCodeInfo‘
set @FunName = ‘SendMessage‘   

--以下参数对应WebService中4个参数的[参数名]
DECLARE @appcode NVARCHAR(50)
DECLARE @password NVARCHAR(50)
DECLARE @sendModes NVARCHAR(50)
DECLARE @title NVARCHAR(50)
DECLARE @content NVARCHAR(50)
DECLARE @needReply VARCHAR(1)
DECLARE @bizObjId NVARCHAR(50)
DECLARE @senderUserID NVARCHAR(50)
DECLARE @receiveUserIds NVARCHAR(50)
DECLARE @receiveAcounts NVARCHAR(50)
DECLARE @sendTime DATETIME
DECLARE @expiredTime DATETIME

SET @appcode=N‘PersonalPortal‘
SET @password=N‘PersonalPortal‘
SET @sendModes=N‘%E7%9F%AD%E4%BF%A1‘
SET @title=N‘test‘
SET @content=N‘test‘
SET @needReply=‘0‘
SET @bizObjId=N‘test‘
SET @senderUserID=N‘工号‘
SET @receiveUserIds=‘‘
SET @receiveAcounts=‘手机号码或邮件‘
SET @sendTime=GETDATE()
SET @expiredTime=DATEADD(HOUR,1,GETDATE())

set @ServiceUrl = @UrlAddress + @FunName + ‘?appcode=‘ + @appcode +‘&‘  
+ ‘password=‘ + @password +‘&‘ 
+ ‘sendModes=‘ + @sendModes +‘&‘ 
+ ‘title=‘ + @title +‘&‘ 
+ ‘content=‘ + @content +‘&‘ 
+ ‘needReply=‘ + @needReply +‘&‘ 
+ ‘bizObjId=‘ + @bizObjId +‘&‘ 
+ ‘senderUserID=‘ + @senderUserID +‘&‘ 
+ ‘receiveUserIds=‘ + @receiveUserIds +‘&‘ 
+ ‘receiveAcounts=‘ + @receiveAcounts +‘&‘ 
+ ‘sendTime=‘ + CONVERT(VARCHAR(50),@sendTime,120)  +‘&‘     
+ ‘expireTime=‘ +   CONVERT(VARCHAR(50),@expiredTime,120)                     
 
 PRINT   @ServiceUrl           


Declare @Object as Int
Declare @ResponseText as Varchar(8000)
                  
Exec sp_OACreate ‘MSXML2.XMLHTTP‘, @Object OUT;
Exec sp_OAMethod @Object, ‘open‘, NULL, ‘get‘,@ServiceUrl,‘false‘
Exec sp_OAMethod @Object, ‘send‘
Exec sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUT
     
Select @ResponseText     
Exec sp_OADestroy @Object