启动 OLE Automation Procedures
sp_configure 'show advanced options', 1;
--此选项用来显示sp_configure系统存储过程高级选项,当其值为1时,可以使用sp_configure列出高级选项。默认为0;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ole Automation Procedures', 1;
--此选项可指定是否可以在Transact-SQL批处理中实例化OLEAutomation 对象。
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'Ole Automation Procedures';
--查看OLE Automation Procedures的当前设置。
GO
如果设置失败 可尝试使用sys账号登录数据库再次执行
样例
USE [DB_NAME]
GO
/****** Object: StoredProcedure [dbo].[SP_NAME] Script Date: 2020-04-29 22:29:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create Procedure [dbo].[SP_NAME]
@IN_ORDERNO varchar(20),--订单号
@ResponseText varchar(8000) OUTPUT
as
declare @ServiceUrl as varchar(1000)
set @ServiceUrl = 'http://IP:18082/*/*/*'
DECLARE @data varchar(max);
set @data='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<procWMSRequest xmlns="http://data.ws.datahub/">
<wmsSecurityInfo xmlns="">
<password>password</password>
<username>username</username>
</wmsSecurityInfo>
<wmsParam xmlns="">
<customerid>EXPRESS_CLIENT</customerid>
<messageid>EXPRESS_T</messageid>
<param>'+@IN_ORDERNO+'</param>
<stdno>EXPRESS_T</stdno>
<warehouseid>WH01</warehouseid>
</wmsParam>
</procWMSRequest>
</soapenv:Body>
</soapenv:Envelope>'
Declare @Object as Int
--Declare @ResponseText AS varchar(1000) ;
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','text/xml;charset=UTF-8'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText
EXEC sp_OAGetErrorInfo @Object --异常输出
Select @ResponseText
Exec sp_OADestroy @Object
GO