sql server 存储过程httpget请求调用链接访问

         1. 

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.

   -- 创建httpget存储过程
   USE [THS_WATER]
   GO
   /****** Object:  StoredProcedure [dbo].[pro_HttpRequestData]    Script Date: 05/13/2019 13:32:20 ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE PROCEDURE [dbo].[pro_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

   3.

   --测试调用 注意 链接必须是 免登录的链接 
   DECLARE @return_value int,
   		@status int,
   		@returnText varchar(2000),
   		@baseURL varchar(max)
   EXEC	@return_value = [dbo].[pro_HttpRequestData]
   		@URL = 'http://127.0.0.1:8080/ths/httpexec.htl?id=123456',
   		@status = @status OUTPUT,
   		@returnText = @returnText OUTPUT;
            SET @returnText = @return_value;
           print @returnText;
   --返回0 说明执行成功，也可也查看调用的方法是否调用