SQL Procedure working when run manually, not running from sql server agent

I have a 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:

The procedure runs without issue when executed manually

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

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

When the query is run seperately 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.

more ▼

asked Apr 15, 2013 at 03:46 PM in Default

avatar image

0 1 1 1

How do you create the SQL Agent job? And how long is the command (in characters/bytes)?

Just wondering if there's a GUI limitation somewhere...

Apr 15, 2013 at 04:06 PM ThomasRushton ♦♦

You might also check your SSIS metadata and/or variables. Check both the length and data type. It may not be passing the full string into the procedure and may also have the wrong data type specified.

Apr 16, 2013 at 08:23 PM KenJ
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 15, 2013 at 03:46 PM

Seen: 1424 times

Last Updated: Apr 17, 2013 at 02:20 PM

Copyright 2018 Redgate Software. Privacy Policy