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)

• Shakeel Hussain Mir: 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)
• Shakeel Hussain Mir: when i remove the above portion of code in the proc works fine
• Shakeel Hussain Mir: i am Using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

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.

• Sir Swears-a-lot: Thanks, that worked for me. FYI: The URL you refer to no longer works.

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.

• Shakeel Hussain Mir: i have used different accounts to check that issue like 'SA'

Answer 4

Adding on sql Agent commang job

SET TEXTSIZE 2147483647;
exec (your proc name)

works

• mustaccio: This doesn't seem to add anything to existing answers.