Response data truncation when running SP through job
<p><strong>Subject:</strong></p>

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

<ol>
<li>Get an XML from our service and store it in DB;</li>
<li>Parse XML and create file.</li>
</ol>

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

<p><strong>Problem:</strong></p>

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

<p>I got Stored Procedure which gets XML:</p>

<pre><code>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)+'&amp;df='+CONVERT(nvarchar(10),@CurActDate,104)+'&amp;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
</code></pre>

<p>When I run this SP like</p>

<pre><code>EXEC [Structure].[GetXML] '2016-06-01', '2016-07-01'
</code></pre>

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

<p>Here is creation script of a table:</p>

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

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

<p><strong>What have I tried:</strong></p>

<ol>
<li><em>Run as user</em> with my account in Job Step properties;</li>
<li>Job was started by schedule or manually;</li>
<li>Add <code>WITH EXECUTE AS OWNER</code> in SP;</li>
<li>Tried using <code>WinHttp.WinHttpRequest.5.1</code> and <code>MSXML2.XMLHTTP</code>.</li>
</ol>

<p><strong>Question:</strong></p>

<p>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?</p>

<p><strong>Note:</strong></p>

<p>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.</p>