2

I am writing a procedure take the data from the web service. The web services gives the data in an XML string. However, It is truncating the data being pulled from the website. It runs perfectly when executed in SSMS. It truncated the data to 2048 characters when run through a batch file and truncated the data to 512 characters when run using a SS Job.

These values can not be random. There must be a setting somewhere but I can't find it. Any ideas?

Here's the code:

    DECLARE 

    @url nvarchar(max),
    @win integer,
    @hr integer ,
    @Date date,
    @SearchDate nvarchar(50)

    Set @Date = GETDATE()

    set @SearchDate = CAST(@Date as nvarchar(50))

    set @SearchDate = REPLACE(@SearchDate,'-','')

    CREATE TABLE TextData([SEMO_Data] [nvarchar](max) NULL)


    Select @url = 'http://semorep.sem-o.com/DataCollection/DataSets.asmx/queryDatasetXML?DatasetName=SET_CAL&User=primplims@gmail.com&Password=testsemo&FromDate=20130103&ToDate=20130111&P1=Energy&P2=Statements&P3=Initial&P4=&P5='

    EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

    EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

    EXEC @hr=sp_OAMethod @win,'Send'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

    INSERT XMLParsing.dbo.TextData(SEMO_Data)
    EXEC @hr=sp_OAGetProperty @win,'ResponseText'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OADestroy @win 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

This is a follow on from my last question. Am hoping that it will be seen easier if I post a new question.

Jennifer Nolan
  • 123
  • 1
  • 8
  • This might help? http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated – IndoKnight May 01 '13 at 13:00
  • @Indoknight Thanks for that. Unfortunately that answer is for a variable, whereas the issue I'm having is with a table. The web service stores the data into a single nvarchar(max) cell in a table. I've already increase any character limits I could find with no luck. It's also already working in SSMS, just truncating when run as a batch file or job. – Jennifer Nolan May 01 '13 at 13:10

1 Answers1

0

I know it's too late, 2022, but i had the same problem today, this solved for me:

https://stackoverflow.com/a/38918970/1750527

Spoiler: queries run outside SSMS has a default text size of 2048. I needed to add a

SET TEXTSIZE 2147483647;

before my script so it run properly.

Crystian Leão
  • 695
  • 1
  • 8
  • 18