SQL Server 访问URL 调用WebServer
时间：2021-07-01 10:21:17
帮助过：87人阅读
/**
存储过程发起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
