SQL Server 访问URL 调用WebServer

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server 访问URL 调用WebServer，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4500字，纯文字阅读大概需要7分钟。

内容图文

/** 存储过程发起URL请求 启用 Ole Automation Procedures 选项 exec sp_configure ‘show advanced options‘,1; go reconfigure; go sp_configure ‘Ole Automation Procedures‘,1; go reconfigure; go */ ALTER PROC P_Url_SendRequest ( @Url VARCHAR(8000) = ‘‘ , @PostData VARCHAR(8000) = ‘‘ , @ResponseText VARCHAR(8000) = ‘‘ OUTPUT ) AS SET NOCOUNT ON DECLARE @ServiceUrl AS VARCHAR(1000) DECLARE @UrlAddress VARCHAR(500) , @ErrMsg VARCHAR(5000) SET @ServiceUrl = @Url PRINT @ServiceUrl DECLARE @Object AS INT , @status INT , @returnText AS VARCHAR(8000) , @HttpStatus VARCHAR(200) , @HttpMethod VARCHAR(20) = ‘get‘ IF ISNULL(@PostData, ‘‘) <> ‘‘ SET @HttpMethod = ‘post‘ /*初始化对*/ EXEC @status = sp_OACreate ‘Msxml2.ServerXMLHTTP.3.0‘, @Object OUT; IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘初始化对象失败，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 1 END /*创建链接*/ EXEC @status = sp_OAMethod @Object, ‘open‘, NULL, @HttpMethod, @ServiceUrl, ‘false‘ IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘创建连接失败，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 2 END SELECT @HttpMethod IF @HttpMethod = ‘post‘ BEGIN --EXEC @status = sp_OAMethod @Object, ‘setRequestHeader‘, -- ‘Content-Type‘, ‘application/x-www-form-urlencoded‘ EXEC @status = sys.sp_OAMethod @Object, ‘setRequestHeader‘, NULL, ‘Content-Type‘, ‘application/x-www-form-urlencoded‘; END ELSE BEGIN EXEC @status = sp_OAMethod @Object, ‘setRequestHeader‘, NULL, ‘Content-Type‘, ‘text/xml; charset=gb2312‘ PRINT @status END IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘设置RequestHeader属性失败，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 2 END EXEC @status = sp_OAMethod @Object, ‘send‘, NULL, @PostData IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘发送请求头失败，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 3 END EXEC @status = sys.sp_OAGetProperty @Object, ‘Status‘, @HttpStatus OUT; IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘读取[Status]属性值失败，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 3 END IF @HttpStatus <> 200 BEGIN SET @ErrMsg = ‘访问错误，http状态代码，‘ + @HttpStatus RAISERROR(@ErrMsg,16,1); RETURN -6; END EXEC @status = sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUT IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘获取回复报文失败，‘ + ISNULL(@ErrMsg, ‘‘) + ISNULL(@returnText, ‘‘) + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 4 END EXEC @status = sp_OADestroy @Object IF @status <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnText OUT SET @ErrMsg = ‘释放资源对象，‘ + @ErrMsg + ISNULL(@returnText, ‘‘) RAISERROR(@ErrMsg,16,-1) RETURN 5 END
RETURN 0 GO

declare @ServiceUrl as varchar(1000) 
declare @UrlAddress varchar(500)

--WebService地址：以http开头，结尾带斜杠，例如‘http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/‘ 
set @UrlAddress = ‘http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/‘

declare @FunName varchar(50)
--WebService中调用的方法名：例如‘getMobileCodeInfo‘
set @FunName = ‘getMobileCodeInfo‘   

--以下参数对应WebService中4个参数的[参数名]
declare @P1 varchar(800),@P2 varchar(100)
set @P1 = ‘mobileCode‘
set @P2 = ‘userid‘

declare @P1_Value varchar(100),@P2_Value varchar(100)
set @P1_Value = ‘13800138000‘
set @P2_Value = ‘‘

set @ServiceUrl = @UrlAddress + @FunName + ‘?‘ + @P1 + ‘=‘ + @P1_Value +‘&‘ + @P2 + ‘=‘ + @P2_Value                       
                  
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
Exec sp_OACreate ‘MSXML2.XMLHTTP‘, @Object OUT;
Exec sp_OAMethod @Object, ‘open‘, NULL, ‘get‘,@ServiceUrl,‘false‘
Exec sp_OAMethod @Object, ‘send‘
Exec sp_OAMethod @Object, ‘responseText‘, @ResponseText OUTPUT
     
Select @ResponseText     
Exec sp_OADestroy @Object
GO

 

SQL Server 访问URL 调用WebServer

标签：请求头   var   tomat   info   post   bsp   初始化   services   output   

本文系统来源：https://www.cnblogs.com/lgx5/p/9695109.html

内容总结

以上是互联网集市为您收集整理的SQL Server 访问URL 调用WebServer全部内容，希望文章能够帮你解决SQL Server 访问URL 调用WebServer所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---