Questions about the sp_OAMethod parameter varchar(8000)

[ChenLin]

alter  PROC PrUrlSendRequest(@Url VARCHAR(8000) = '',@PostData VARCHAR(10) = '' ,@ResponseText VARCHAR(8000) = '' OUTPUT)AS
    /*存储过程发起URL请求,启用 Ole Automation Procedures 选项
	exec sp_configure 'show advanced options',1;
	go
	reconfigure;
	go
	sp_configure 'Ole Automation Procedures',1;
	go
	reconfigure;
	go
	*/
	SET NOCOUNT ON 
    DECLARE @ErrMsg VARCHAR(5000)
    DECLARE @Object INT,@status INT,@returnTextErr VARCHAR(8000),@HttpStatus VARCHAR(200),@HttpMethod VARCHAR(10) = '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, @returnTextErr OUT
            SET @ErrMsg = '初始化对象失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
            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, @returnTextErr OUT
            SET @ErrMsg = '创建连接失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
            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, @returnTextErr OUT
            SET @ErrMsg = '设置RequestHeader属性失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
            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, @returnTextErr OUT 
            SET @ErrMsg = '发送请求头失败，' + @ErrMsg + ISNULL(@returnTextErr, '')
            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, @returnTextErr OUT 
            SET @ErrMsg = '读取[Status]属性值失败，' + @ErrMsg + ISNULL(@returnTextErr,'')
            RAISERROR(@ErrMsg,16,-1)           
            RETURN 3
        END

    IF @HttpStatus <> 200
        BEGIN
            SET @ErrMsg ='访问错误,http状态代码,'+@HttpStatus+''
            RAISERROR(@ErrMsg,16,1);
            RETURN -6;
        END

    --问题在这里：@ResponseText 不支持超过8000字符
	declare @json varchar(max)
	EXEC @status = sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
	--返回查询结果
	select  @ResponseText

    IF @status <> 0
        BEGIN  
            EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
            SET @ErrMsg = '获取回复报文失败：' + ISNULL(@ErrMsg, '') + '，'+ ISNULL(@returnTextErr, '')
            RAISERROR(@ErrMsg,16,-1)           
            RETURN 4
        END 

    EXEC @status = sp_OADestroy @Object
    IF @status <> 0
        BEGIN  
            EXEC sp_OAGetErrorInfo @Object, @ErrMsg OUT, @returnTextErr OUT
            SET @ErrMsg = '释放资源对象，' + @ErrMsg + ISNULL(@returnTextErr, '')
            RAISERROR(@ErrMsg,16,-1)           
            RETURN 5
        END
    RETURN 0
GO

declare @url as Varchar(2000) ,@ResponseText as Varchar(8000)

set @url='http://cache.video.iqiyi.com/jp/avlist/202861101/1/?callback=jsonp9'
exec PrUrlSendRequest @url ,'',@ResponseText OUTPUT


The following test will occur because there are too many results returned. "The source data specified for this string, binary column, or parameter is too long."
Can anyone help solve this problem?

[sapator]

Try setting "declare @url as NVarchar(2000)" , if this does not work try with Nvarchar(MAX) or Varchar(MAX) but I guess it should be nvarchar if you are passing data in Chinese.

[wqweto]

Try this

    --问题在这里：@ResponseText 不支持超过8000字符
    DECLARE @json TABLE (Result NVARCHAR(MAX))
    INSERT  @json(Result)
    EXEC    @status = dbo.sp_OAGetProperty @Object, 'responseText'
    SELECT  @ResponseText = Result FROM @json

cheers,
</wqw>

[ChenLin]

Try this

    --问题在这里：@ResponseText 不支持超过8000字符
    DECLARE @json TABLE (Result NVARCHAR(MAX))
    INSERT  @json(Result)
    EXEC    @status = dbo.sp_OAGetProperty @Object, 'responseText'
    SELECT  @ResponseText = Result FROM @json

cheers,
</wqw>

Thank you so much for wqweto! Give me a good solution. After testing, it works perfectly well. Thank you again for wqweto.