declare @url varchar(1000), @data varchar(1000)
SELECT SYSDATE.PCDATE
FROM
(
SELECT CONVERT(CHAR(10), DATEADD(dd, number, '2018-12-30'), 120) AS PCDATE
FROM master.dbo.spt_values AS spt
WHERE type = 'p' AND number <= 2
) AS SYSDATE
set @url='http://api.goseek.cn/Tools/holiday?date='
set @data='20190505'
set @url=@url+@data
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',@url,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, null --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Exec sp_OADestroy @Object
select @ResponseText
--select JSON_VALUE(@ResponseText, '$.data');
--SQL SERVER 2016一下不支持JSON时使用
SELECT SUBSTRING(@ResponseText,CHARINDEX('data',@ResponseText)+6,1)
--帮助
-- 1. SQL Server 阻止了对组件 'Ole Automation Procedures' 的 过程'sys.sp_OACreate' 的访问
--开启 Ole Automation Procedures
use master
go
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
----------------------------------------------------------------------------------