SQL Server 访问URL 调用WebServer
以下整理的SQL Server中访问URL地址的方法,并已封装成存储过程,可以实现POST/GET请求
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO /** 存储过程发起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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤