Stored procedure working when run manually, not running from sql server agent

Question

I have a stored procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error.

line 9, character 9, unexpected end of input

The code takes a very long XML string in UTF-8 encoding and puts it into a single nvarchar(max) cell. Then puts this string into a XML cell in a different table, allowing me to query the individual parts of the XML code using the nodes function. I cannot put the data directly into a nvarchar cell due to encoding differences.

I can't reproduce the string here as it is very very long.

I'm just looking for ideas really as to where it might be going wrong.

Here is what I know so far:

1. The procedure runs without issue when executed manually

2. I have checked permission issues, and that doesn't seem to be the problem. The agent runs under my own account and I am a sysadmin on the database

3. I split the procedure into separate parts to locate exactly where the problem is occurring. Once again the separate procedures run fine when executed manually but an error occurs when run through SQL Server agent.

4. When the query is run separately through SQL Server Agent it gives a slightly different error. This leads me to believe it is an encoding issue. However I am getting the XML from a webpage and I can't change the encoding on the webpage.

line 1, character 38, unable to switch the encoding

I know this is a long shot since you can't replicate the issue but if anyone could give an idea as to where to start looking for an answer, it would be greatly appreciated.

Edit1: Thank you so much for your help. I had to take a little time away from the problem to clear my head. Back at it this week.

Ok I think I've finally figured out the root of the problem. This is the code I am using to take the data from the website. 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 truncates 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)

Delete from XMLParsing.dbo.TextData

Set @Date = GETDATE()

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

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

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 

Answer 1

Specify

SET TEXTSIZE 200000 

in the SQL Agent job step. The default TEXTSIZE for SQL Agent is 512.

Answer 2

Just an update. The reason this wasn't working correctly is that the XML being taken from the site was being truncated. I never fully figured out why this was happening but I used wget for windows instead of WinHttp.WinHttpRequest.5.1 which took all the data from the site and passed it directly to a webpage from which I could parse the data.

Answer 3

Just put SET TEXTSIZE (with a great value)

and SQL Agent do the rest.