1.SQL Server 阻止了对组件“Ole Automation Procedures”的 过程“sys.sp_OACreate”的访问
#开启 Ole Automation Procedures
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
2.调用webservice
declare @ServiceUrl as varchar ( 1000 )
set @ServiceUrl = 'http://localhost:19930/LoginWebService.asmx/Login'
DECLARE @data varchar ( max) ;
set @data = 'userid=1&phone=17647582259'
Declare @Object as Int
Declare @ResponseText AS varchar ( 1000 ) ;
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 , @data
Exec sp_OAMethod @Object , 'responseText' , @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object
Select @ResponseText
Exec sp_OADestroy @Object
GO
3.调用webapi
declare @ServiceUrl as varchar ( 1000 )
set @ServiceUrl = 'http://xxxxx.com/api?userid=6'
DECLARE @data varchar ( max) ;
set @data = ''
Declare @Object as Int
Declare @ResponseText AS varchar ( 8000 ) ;
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0' , @Object OUT ;
Exec sp_OAMethod @Object , 'open' , NULL , 'GET' , @ServiceUrl , 'false'
Exec sp_OAMethod @Object , 'send' , NULL , @data
Exec sp_OAMethod @Object , 'responseText' , @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object
Select @ResponseText
Exec sp_OADestroy @Object
GO
declare @ServiceUrl as varchar ( 1000 )
set @ServiceUrl = 'http://xxx.com/webapi'
DECLARE @data varchar ( max) ;
set @data = 'CityName=SubmitSystemName=%E7%99%BE%E5%BA%A6%E5%8F%8D%E9%A6%88&OriginID=2d90660c-436c-4e12-bfa6-e849a06b2c51&Price=10000&IsAccurate=False&PriceType=1&UserKeyId=a669e4ec7bdc47a7b6c2c334ebe1a50c&signature=X8p3lIZT0Ba3LeiC6irm3%2FMnlE8%3D&time=1452735047291'
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 , @data
Exec sp_OAMethod @Object , 'responseText' , @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object
Select @ResponseText
Exec sp_OADestroy @Object
GO
4.存储过程
CREATE PROC P_Service
@ServiceUrl varchar ( 1000 ) ,
@data varchar ( max) ,
@GetOrPost varchar ( 100 ) ,
@HeadersKey varchar ( 200 ) ,
@HeadersValue varchar ( 200 )
as
Declare @Object as Int
Declare @ResponseText AS varchar ( 1000 ) ;
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0' , @Object OUT ;
Exec sp_OAMethod @Object , 'open' , NULL , @GetOrPost , @ServiceUrl , 'false'
Exec sp_OAMethod @Object , 'setRequestHeader' , NULL , @HeadersKey , @HeadersValue
Exec sp_OAMethod @Object , 'send' , NULL , @data
Exec sp_OAMethod @Object , 'responseText' , @ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
GO