SQL Server 調用 WebService [ASMX]

1.測試 www.baidu.com 

Declare @Object as Int
Declare @ResponseText as nvarchar(4000)
Declare @ServiceUrl varchar(max)='http://www.baidu.com'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; --創建OLE元件物件
    Exec sp_OAMethod @Object, 'open', NULL, 'post',@ServiceUrl,'false' --打開連結，注意是get還是post    
    EXEC sys.sp_OAMethod @object,'setRequestHeader',NULL,'Content-Type','application/x-www-form-urlencoded;charset=UTF-8'
    Exec sp_OAMethod @Object, 'send'
    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT --輸出參數
    Select @ResponseText      --輸出結果
    Exec sp_OADestroy @Object

由于是今天剛裝的SQL Server 所以一切環境都是新的，在測試的過程中遇到了一個比較常見的報錯：

SQL Server 阻止了对组件“Ole Automation Procedures”的 过程“sys.sp_OACreate”的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用“Ole Automation Procedures”。有关启用“Ole Automation Procedures”的详细信息，请搜索 SQL Server 联机丛书中的“Ole Automation Procedures”。

解決辦法：更改服務器配置

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

返回結果 NULL

2.測試 WebService （目前只查到調用 url 的 asmx文件）

功能：檢查QQ是否在線

Declare @Object as Int
Declare @ResponseText as nvarchar(4000)
Declare @ServiceUrl varchar(max)='http://www.webxml.com.cn/webservices/qqOnlineWebService.asmx/qqCheckOnline?qqCode=12345678912'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; --創建OLE元件物件
    Exec sp_OAMethod @Object, 'open', NULL, 'get',@ServiceUrl,'false' --打開連結，注意是get還是post    
    EXEC sys.sp_OAMethod @object,'setRequestHeader',NULL,'Content-Type','application/x-www-form-urlencoded;charset=UTF-8'
    Exec sp_OAMethod @Object, 'send'
    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT --輸出參數
    Select @ResponseText      --輸出結果
    Exec sp_OADestroy @Object

SQL返回結果:  N(不在線)

<?xml version="1.0" encoding="utf-8"?>  
<string xmlns="http://WebXml.com.cn/">N</string>

通用版：

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)

--WebService地址：以http开头，结尾带斜杠，例如'http://www.webxml.com.cn/webservices/qqOnlineWebService.asmx/' 
set @UrlAddress = 'http://www.webxml.com.cn/webservices/qqOnlineWebService.asmx/'

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

--以下参数对应WebService中参数的[参数名]
declare @P1 varchar(800),@P2 varchar(100)
set @P1 = 'qqCode'
--set @P2 = 'userid'

declare @P1_Value varchar(100),@P2_Value varchar(100)
set @P1_Value = '8698053'
--set @P2_Value = ''

set @ServiceUrl = @UrlAddress + @FunName + '?' + @P1 + '=' + @P1_Value --+'&' + @P2 + '=' + @P2_Value                       
                  
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
                  
--创建 OLE 对象的实例。
--sp_OACreate { progid | clsid } , objecttoken OUTPUT [ , context ] 
--返回的对象令牌，并且必须是数据类型为 int 的局部变量。 该对象令牌用于标识所创建的 OLE 对象，并用于调用其他 OLE 自动化存储过程。
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
--sp_OAMethod   调用一个 OLE 对象的方法。
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  --破坏已创建的 OLE 对象。--如果未调用 sp_OADestroy，则在批处理结束时将自动破坏已创建的 OLE 对象。
--当不再需要对象时，调用 sp_OADestroy 释放使用 sp_OACreate 创建的对象实例。
GO

—————————— 2022.05.24 更新：

3.SOAP 如何調用 ？放在 IIS 的 WebService  能用 SP 直接調用嗎？

Declare @Object as Int
Declare @ResponseText as nvarchar(4000)
Declare @ServiceUrl varchar(max)='http://wx-webservicefr/CI000245.asmx?op=PDA_TestHelloWorld'
Declare @RequestText as varchar(8000);
set @RequestText=
'<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <PDA_TestHelloWorld xmlns="http://tripod-tech.com/">
      <CompanyCode>CN01</CompanyCode>
      <FactoryId>C333</FactoryId>
      <SystemId>PDA</SystemId>
    </PDA_TestHelloWorld>
  </soap:Body>
</soap:Envelope>'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; --創建OLE元件物件
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false' --打開連結，注意是get還是post 
EXEC sys.sp_OAMethod @object,'setRequestHeader',NULL,'Content-Type','text/xml;charset=utf-8'
EXEC sp_OAMethod @object, 'setRequestHeader', null, 'SOAPAction', 'http://tripod-tech.com/PDA_TestHelloWorld'
Exec sp_OAMethod @Object, 'send',NULL,@RequestText
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT --輸出參數
Select @ResponseText      --輸出結果
Exec sp_OADestroy @Object

執行結果：

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Body><PDA_TestHelloWorldResponse xmlns="http://tripod-tech.com/">

<PDA_TestHelloWorldResult>

<RSPDA_TestHelloWorld>

<COLUMN1>Hello World！</COLUMN1>

</RSPDA_TestHelloWorld>

<RSPDA_TestHelloWorld />

</PDA_TestHelloWorldResult>

</PDA_TestHelloWorldResponse>

</soap:Body></soap:Envelope>

在IIS的服務器上打開當前ASMX文件，進入傳參界面，設定如下：

服務器上的返回值：

@RequestText （send內容）和 SOAPAction 的來源： 

另，可供參考測試的公開Webservice站點：

http://www.webservicex.net/WS/wscatlist.aspx

http://www.service-repository.com/

http://www.webxml.com.cn/zh_cn/index.aspx

參考資料：

​​​​​​SQL Server 阻止了对组件 'Ole Automation Procedures' 的 过程'sys.sp_OACreate' 的访问

[ZT].Net中動態建立和調用WebServices的方法

sql server 调用webservice

SQL Server 2008 R2 根据WSDL访问WebService

 
推薦閱讀：

在SQL触发器里面调用webservice接口 （內含對參數進行編碼和解碼的自定義函數）

SoapUI使用教程

推薦工具：soapui

測試接口1：http://www.webxml.com.cn/webservices/qqOnlineWebService.asmx/qqCheckOnline?qqCode=12345678912