参考文档：http://blog.csdn.net/madStone_l/article/details/50844447
原代码里有坑，@HttpMethod 内容 “POST”、”GET”必须大写，否则会报错。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
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 */ drop PROC P_Url_SendRequest go create PROC P_Url_SendRequest ( @Url VARCHAR(8000) = '' , @PostData VARCHAR(8000) = '' , @ResponseText VARCHAR(8000) = '' OUTPUT ) AS SET NOCOUNT ON DECLARE @ErrMsg VARCHAR(5000) 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, @Url,'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 IF @HttpMethod = 'POST' BEGIN --EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/x-www-form-urlencoded'; EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8' END ELSE BEGIN --EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'text/xml; charset=gb2312' EXEC @status = sp_OAMethod @Object, 'setRequestHeader', NULL,'Content-Type', 'application/json; charset=UTF-8' 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 |
© 2017, ITJOY.NET. 版权所有. 如未注明，均为原创，转载请注明出处。
“MSSQL访问WebServer接口代码 – P_Url_SendRequest”的2个回复