Response data truncation when running SP through j

Question

Subject:

I've got a report that I create every month. The creation of report consists of 2 steps:

1. Get an XML from our service and store it in DB;
2. Parse XML and create file.

For the last few month I've created report in manual mode. And now I want to automate this stuff. But here comes a

Problem:

The second step (parsing XML and file creation) runs like a charm, but with first step I'm observing weird behaviour.

I got Stored Procedure which gets XML:

ALTER PROCEDURE [Structure].[GetXML]
    @LastActDate date,
    @CurActDate date
AS
BEGIN
    SET NOCOUNT ON;

    begining:

    DECLARE @URI varchar(2000),
            @methodName varchar(50),
            @objectID int,
            @hResult int,
            @setTimeouts nvarchar(255),
            @serv nvarchar(255) = 'http://example.com/docs/',
            @result nvarchar(max) = ''

    DECLARE @t TABLE(Resp nvarchar(max))

    declare @timeStamp nvarchar(50) = convert(nvarchar(50),CURRENT_TIMESTAMP,127)

    declare @CurDate date = dateadd(day,0,getdate())

    --EXEC @hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
    EXEC @hResult = sp_OACreate 'MSXML2.XMLHTTP', @ObjectID OUT

    SELECT  @URI = @serv + '.newchange?ds='+CONVERT(nvarchar(10),@LastActDate,104)+'&df='+CONVERT(nvarchar(10),@CurActDate,104)+'&pardaily=1',
            @methodName='GET',
            @setTimeouts = 'setTimeouts(9000,90000,900000,9000000)' 

    EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false' 
    EXEC @hResult = sp_OAMethod @objectID, @setTimeouts
    EXEC @hResult = sp_OAMethod @objectID, 'send', null 

    INSERT INTO @t
    EXEC sp_OAGetProperty @objectID, 'responseText'

    SELECT top 1 @result = Resp 
    FROM @t

    if @result is null
    begin
        delete from @t
        exec sp_OAGetErrorInfo @objectID
        exec sp_OADestroy @objectID 
        goto begining
    end
    else
    begin
        INSERT INTO Structure.MonthlyRow 
        SELECT @timeStamp, @result 
    end
END

When I run this SP like

EXEC [Structure].[GetXML] '2016-06-01', '2016-07-01'

I got a row in Structure.MonthlyRow table with correct timestamp and response (the average length is ~70k symbols)

Here is creation script of a table:

CREATE TABLE Structure.MonthlyRow(
    [timestamp] nvarchar(50) NOT NULL,
    [RowResp] nvarchar(max) NULL,
 CONSTRAINT [PK_dDayly] PRIMARY KEY CLUSTERED ([timestamp] DESC)) 

If I create a job that launch this SP I get a row in table with results, and the length of result is 512 symbols! It is a proper part of XML that looks like it was truncated from nvarchar(max) to nvarchar(512), but I have no variables or table columns with length of 512 that are used.

What have I tried:

1. Run as user with my account in Job Step properties;
2. Job was started by schedule or manually;
3. Add WITH EXECUTE AS OWNER in SP;
4. Tried using WinHttp.WinHttpRequest.5.1 and MSXML2.XMLHTTP.

Question:

What possibly could be a problem? Why I am getting correct results when I run my SP manually, and got only 512 symbols of response when run SP as job step?

Note:

Yes, I know that getting XML from web-service is better handled by PHP, C# or even PowerShell and if I can not find a solution I will use one of them.

Answer 1

add this line at the top of your sp or in the job before EXEC of your sp

SET TEXTSIZE 2147483647;

the problem is that jobs set a default

SET TEXTSIZE 1024

this limits data returned to 1024 chars (512 for nchars)