SQL Server 访问发送Http请求

为什么80%的码农都做不了架构师？>>>   

-- 通用读取获取数据存储过程

--开启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=错误源编

*/

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;

----测试

  DECLARE @returnText VARCHAR(500)

  DECLARE @status int

  DECLARE @urlStr VARCHAR(255)

  SET @urlStr = 'http://www.webxml.com.cn/WebServices/WeatherWebService.asmx/getSupportCity?byProvinceName=广东';

EXEC P_GET_HttpRequestData  @urlStr, @status OUTPUT, @returnText   OUTPUT;

print  @returnText ; ---打印

转载于:https://my.oschina.net/jon/blog/297130