sql调用webservice问题

    declare @ServiceUrl as varchar(1000) 
    declare @UrlAddress varchar(500)
    declare @FunName varchar(50)
    declare @P1 varchar(20)
    declare @P2 varchar(20)
    declare @P3 varchar(20)
    declare @P1_Value varchar(20)
    declare @P2_Value varchar(20)
    declare @P3_Value varchar(100)
    select @UrlAddress='http://192.168.0.103:8000/ForSRMK2Service.asmx/'
set @FunName = 'PerformCountermeasureProcess'   
set @P1 = 'Term'
set @P2 = 'Pitem'
    set @P3 ='firstRespon'
set @P1_Value ='asdf'
set @P2_Value = 1111
    select  @P3_Value ='sailunjinyu\buqinglong'
set @ServiceUrl = @UrlAddress + @FunName + '?' + @P1 + '=' + @P1_Value +'&' + @P2 + '=' + @P2_Value  +'&' + @P3 + '=' + @P3_Value                       
Declare @Object as Int
Declare @ResponseText as nvarchar(4000)
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

返回结果:

<?xml version="1.0" encoding="utf-8"?>
<boolean xmlns="http://192.168.0.103:8000/">true</boolean>

根据返回结果处理业务

 if    charindex('TRUE',upper(@ResponseText) ) >0
    begin
      -- DoSomething
        If @@Error <> 0
Goto ErrM 
    end


此方法并不适用所有webservice,本机http://localhost:8001/WebService.asmx下的

报错,返回结果:

因 URL 意外地以“/CurrentTime”结束,请求格式无法识别

不明所以


   
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
DECLARE @Object int; DECLARE @HR int; DECLARE @Property nvarchar(255); DECLARE @Return nvarchar(255); DECLARE @Source nvarchar(255), @Desc nvarchar(255); DECLARE @httpStatus int; DECLARE @response varchar(8000); --创建 OLE 对象的实例 EXEC @HR = sp_OACreate N'MSXML2.XMLHTTP.6.0',@Object OUT; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('Error Creating COM Component 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO END_ROUTINE END BEGIN --Open EXEC @HR = sp_OAMethod @Object,N'open',Null,'GET','http://localhost:1728/HttpServer/submit.aspx',FALSE; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('Open 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END --setRequestHeader EXEC @HR = sp_OAMethod @Object,N'setRequestHeader',Null,'Content-Type','text/xml'; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('setRequestHeader 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END --send EXEC @HR = sp_OAMethod @Object,N'send',Null,''; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('send 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END --readyState EXEC @HR = sp_OAGetProperty @Object,'readyState', @httpStatus OUT; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('readyState 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END --verify status IF @httpStatus 4 BEGIN RAISERROR('readyState http status bad', 16,1) GOTO CLEANUP END --status EXEC @HR = sp_OAGetProperty @Object,'status', @httpStatus OUT; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('getstatus 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END --verify status IF @httpStatus 200 BEGIN Print Cast(@httpStatus As varchar) RAISERROR('Open http status bad', 16,1) GOTO CLEANUP END --responseText EXEC @HR = sp_OAGetProperty @Object, 'responseText', @response OUT IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; RAISERROR('responseText 0x%x, %s, %s',16,1, @HR, @Source, @Desc) GOTO CLEANUP END Print @response END CLEANUP: BEGIN EXEC @HR = sp_OADestroy @Object; IF @HR 0 BEGIN EXEC sp_OAGetErrorInfo @Object,@Source OUT,@Desc OUT; SELECT HR = convert(varbinary(4),@HR),Source=@Source,Description=@Desc; END END END_ROUTINE: RETURN; GO

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值