SqlServer--get 和 post 请求 http接口

1. 开启

不开启报错 如下 4.1 SQL Server blocked access to procedure ‘sys.sp_OACreate’

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. post

Declare @ServiceUrl nvarchar(MAX) 
Declare @req_data nvarchar(MAX) 
Declare @data nvarchar(MAX) 
set @ServiceUrl = 'http://**.**.**.***:****/Portal/Webservices/TESTService.asmx/TEST'
set @req_data='req_data={"DJH":"SPJHD201121015","SWLXID":"0211","SWLX":"%E5%80%9F%E8%B4%A7%E5%87%BA%E5%BA%93","SWRQ":"2020-11-21","CKKCZZID":"000001","CKKCZZ":"%E6%8B%9B%E8%BF%9C%E5%B1%95%E5%8E%85","CKCKID":"000198","CKCK":"%E6%91%86%E4%BB%B6%E6%88%90%E5%93%81%E4%BB%93%E5%BA%93","CWFLID":"CWWPFL025","CWFL":"%E9%87%91%E6%91%86%E4%BB%B6","XQDWMC":"%E7%8E%8B%E6%99%93%E5%AE%81","DYMS":"%E5%8A%A0%E7%9B%9F%E9%83%A8%E5%86%85%E9%83%A8%E5%80%9F%E8%B4%A7%E8%B6%85%E4%B8%80%E5%A4%A9%E7%9A%84","JHLX":"%E5%B1%95%E9%94%80%E5%80%9F%E8%B4%A7","YWGK":"%E5%8A%A0%E7%9B%9F%E9%83%A8","YJDQRQ":"2020-11-24","BZSM":"%E6%B1%9F%E8%8B%8F%E5%B1%95%E4%BC%9A%32%30%32%30%2E%31%31%2E%32%32%2D%32%34%E6%97%A5","LSBomrows":[{"XHB":1,"TMB":"HW000464896","WPBMB":"%30%30%32%32%34%36","WPMCB":"%E8%B6%B3%E9%87%91%39%39%39%E6%91%86%E4%BB%B6","GLFSB":"%E5%8D%95%E4%BB%B6%E7%AE%A1%E7%90%86","SLB":1.00,"JZB":50.620,"ZLB":50.620,"XSZGFDJ1B":0.00,"XSZGFDJ2B":0.00,"XSZGFB":0.00,"XSJGFDJB":0.00,"XSJGFB":0.00,"BZB":""},{"XHB":2,"TMB":"HW000464897","WPBMB":"%30%30%32%32%34%36","WPMCB":"%E8%B6%B3%E9%87%91%39%39%39%E6%91%86%E4%BB%B6","GLFSB":"%E5%8D%95%E4%BB%B6%E7%AE%A1%E7%90%86","SLB":1.00,"JZB":52.330,"ZLB":52.330,"XSZGFDJ1B":0.00,"XSZGFDJ2B":0.00,"XSZGFB":0.00,"XSJGFDJB":0.00,"XSJGFB":0.00,"BZB":""}]}'
 
Declare @Object as Int
Declare @ResponseText as Varchar(8000)
  
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
 
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'
 
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
 
Exec sp_OAMethod @Object, 'send', NULL, @req_data --发送数据
 
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
 
EXEC sp_OAGetErrorInfo @Object --异常输出
 
Select  @ResponseText
 
Exec sp_OADestroy @Object
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3. get

 
 
Declare @ServiceUrl nvarchar(MAX) 
Declare @req_data nvarchar(MAX) 
Declare @data nvarchar(MAX) 
set @ServiceUrl = 'http://**.**.**.***:****/Portal/Webservices/TESTService.asmx/TEST?req_data=teststr'
 
 
 
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 @Object
EXEC sp_OAGetErrorInfo @Object --异常输出
 
select @ResponseText
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4. error

4.1 SQL Server blocked access to procedure ‘sys.sp_OACreate’

解决方案-参考链接