1.要使用这个功能需要先开启Sql Server 通讯配置--
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
2.建一个存储过程[P_GET_HttpRequestData]
GO
/****** Object: StoredProcedure [dbo].[P_GET_HttpRequestData] Script Date: 11/21/2019 08:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[P_GET_HttpRequestData](
@URL varchar(500),
@status int=0 OUT
)
AS
BEGIN
DECLARE @object int,
@errSrc int
/*初始化对*/
declare @str nvarchar(200)
declare @ResponseText nvarchar(200)
EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT;
print '===================='
print @object
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
/*创建链接*/
EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
/*发起请求*/
EXEC @status= SP_OAMethod @object,'send',null
print '--------------------------------'
print @str
print '--------------------------------'
print @status
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT --@ResponseText为http返回的内容
Select @ResponseText
print @ResponseText
Exec sp_OADestroy @Object
END;
3.调用存储过程执行Url
declare @url varchar(1000)
declare @ddip varchar(100)
declare @vipno varchar(100)
--取IP地址
SELECT @ddip=sysvalue FROM s_sys where syscode='ddip'
--取需要同步的会员编号
select @vipno=c_vipno from inserted
--拼接url地址
set @url='http://'+@ddip+'/DDAPI.aspx?type=tongbuVip&&bh='+@vipno
执行url
EXECUTE P_GET_HttpRequestData @url