Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Response data truncation when running SP through job

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.

like image 357
gofr1 Avatar asked Aug 03 '16 09:08

gofr1


People also ask

How to Fix String or binary data would be truncated?

How to fix “String or binary data would be truncated” The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size.


1 Answers

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)

like image 84
MtwStark Avatar answered Sep 28 '22 04:09

MtwStark