4

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)
  • 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 Jan 27 '16 at 10:33
  • when i remove the above portion of code in the proc works fine – Shakeel Hussain Mir Jan 27 '16 at 10:33
  • 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) – Shakeel Hussain Mir Sep 26 '16 at 4:57
2

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.

1

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

0

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.

  • i have used different accounts to check that issue like 'SA' – Shakeel Hussain Mir Jan 27 '16 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.