3

I have a piece of code that need to run as a recurrent SQL job.

When running the code as simple query inside the SQL Server query editor it runs successfully and return the expected results (merging several lines from the XML into an existing table).

But when creating a new job for SQL Server Agent, adding a single step that need to run the exact same script, it always fails when trying to execute

INSERT #xml ( [XMLData] )

Error message is:

Executing as user 'myUser': XML Parsing: line 19 column 0, unexpected end of input.

If the XML was invalid in any way then this error message would appear when executing the same line in it's script version. It must be something else.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zack
  • 119
  • 2
  • 13
  • It's a file that I read from a remote domain into my database server. Is that answering your question? – Zack Sep 09 '15 at 14:39
  • Can you post the content of the file and a screenshot of your SSIS package? – Code Different Sep 09 '15 at 14:41
  • Sorry, not sure if I'm allowed to publish the XML here. Have to ask permission from my boss. Will have to wait for tomorrow. Thanks anyway. – Zack Sep 09 '15 at 15:02

1 Answers1

2

I had this same issue when Google Geocoding and parsing the XML result. It seems that when run via an SQL job, the XML result is being truncated. I found the solution here.

You have to put the following at the start of your stored procedure:

SET TEXTSIZE 2147483647;
TT.
  • 15,774
  • 6
  • 47
  • 88
John
  • 21
  • 3