Stored procedure working when run manually, not running in SQL Agent Job

Question

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message:

Executed as user: NT AUTHORITY\SYSTEM.
XML parsing: line 10, character 33, unexpected end of input [SQLSTATE 42000] (Error 9400).
The step failed.
Sql Severity 16, Sql Message ID 9400

However, when I run it manually, it is successful.

This is the procedure which I am executing:

decalre @URL VARCHAR(max)
 set @URL='http://www.spa.gov.sa/english/rss.xml'

 declare  @xmlT TABLE ( yourXML XML )
DECLARE @Response nvarchar(max)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg nvarchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

INSERT @xmlT ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 


    INSERT into Tlb(discp , tit ,   datee,linkk)
    SELECT   N.C.value('description[1]', 'nvarchar(max)') discp,   N.C.value('title[1]', 'varchar(999)') tit,
    N.C.value('pubDate[1]', 'varchar(99)') datee,N.C.value('link[1]', 'varchar(999)') linkk
    FROM @xmlT CROSS APPLY yourXML.nodes('//channel/item') N(C)

Answer 1

I had this same issue when google geocoding and parsing the XML result. Seems that when run via an SQL job the XML result was being truncated.

Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Had to put:

SET TEXTSIZE 2147483647;

at the start of my stored proc.

Answer 2

In a very specific situation the absolute correct answer is to define the TEXTSIZE inside of the stored procedure.

The scenario is that a query will execute without issues in management studio. It will execute as a stored procedure in management studio but when run as a SQL job an error is returned and the SQL job history shows 'unexpected end of input.' My error returned line 1 and position 512 which seems significant but i have seen many other reports of this occurring in other positions.

The answer posted above is ENTIRELY CORRECT and i would have just acknowledged it if it was possible with a new account.

This is one of those where you really want to thank the person who had the answer to the problem you've been wrestling with for hours. Hopefully someone benefits and thank you very much to previous posters.

SET TEXTSIZE 2147483647

Answer 3

For OLE automation stored procedures you need to run as a user that's part of the sysadmin server role. I would venture to guess that currently it fails and doesn't form a correct XML. Run the job step as a different user and see if that fixes the problem.

From BOL:

Permissions Requires membership in the sysadmin fixed server role.

Answer 4

Adding on sql Agent commang job

SET TEXTSIZE 2147483647;
exec (your proc name)

works