SQL Server 访问发送Http请求

SQL Server 访问发送Http请求  



-- 通用读取获取数据存储过程
--开启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

/*
参数说明?
@URL=http请求地址
@status=状态代?
@returnText=返回?
@object=对象令牌
@errSrc="https://my.oschina.net/acitiviti/blog/错误源编?
*/
CREATE PROCEDURE P_GET_HttpRequestData(
	@URL varchar(500),
	@status int=0 OUT,
	@returnText varchar(2000)="'' OUT
)
AS
BEGIN
	DECLARE @object int,
	@errSrc int
	/*初始化对*/
	EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT
	IF @status <> 0
	BEGIN
	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
	 RETURN
	END

	/*创建链接*/
	EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
	IF @status <> 0
	BEGIN
	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
	 RETURN
	END
	EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
	/*发起请求*/
	EXEC @status= SP_OAMethod @object,'send',NULL 
	IF @status <> 0 
	BEGIN 
	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
	 RETURN
	END
     
	/*获取返回*/
	EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT
	IF @status <> 0 
	BEGIN 
	 EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
	 RETURN
	END
END;

----测试存储过程
CREATE PROCEDURE IPCC_WEBSERVICE_PACKID(@parametersStr VARCHAR(255),@outText VARCHAR(255) OUT)
AS
BEGIN
  DECLARE @returnText VARCHAR(500)
  DECLARE @status int
  DECLARE @urlStr VARCHAR(255)
  SET @urlStr = 'http://172.17.5.14:9090/df/sendDataToIVR.action?parametersStr='+@parametersStr+'&ifaceType=4';
EXEC P_GET_HttpRequestData    @urlStr, @status OUTPUT, @returnText   OUTPUT;
   SET @outText = @returnText;
   print @outText ; ---打印
END;

测试：

EXEC IPCC_WEBSERVICE_PACKID 'para',''

   DECLARE @returnText VARCHAR(500)
  DECLARE @status int
  DECLARE @urlStr VARCHAR(255)
 EXEC P_GET_HttpRequestData 'http://192.168.1.113:8080/EquipmentDepreciationDistribution/servlet /SelectUnCloseCostPeriodServlet', @status OUTPUT, @returnText   OUTPUT;
 print(@returnText)

para是拼接URL的参数，后面空参数是最后输出打印定义的一个参数，这个参数最后会被替换，所以随便输一个

 

文章来源：http://ju.outofmemory.cn/entry/292124