Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Procedure working manually, not using agent

Author  Topic 

selphie_cat
Starting Member

4 Posts

Posted - 2013-04-15 : 11:54:01
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 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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-15 : 12:36:06
quote:
Originally posted by selphie_cat

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 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.



Are you getting the error when inserting the string into the nvarchar(max) column, or are you getting the error in the next step where you convert it to XML? I suspect the latter.

My initial reaction would have been that it is an encoding problem. For example, if your XML specifies UTF-8 (you can tell if it is specifying UTF-8 by looking at the first line which would be this: <?xml version="1.0" encoding="UTF-8"?>) and if you had characters in the string that cannot be represented in UTF-8 (such as accented characters) you would get the error you posted.

However, since you are able to take that exact text and convert that to XML when run interactively, that theory does not hold water. Are you sure it is the same string? In any case change the encoding or remove the encoding specification (using T-SQL replace function) before inserting into the XML column.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-15 : 15:04:45
- who is the job owner? if not an admin that maybe it.
- by what means are you "getting the XML from a webpage" ? If it by CLR or xp_cmdshell then it still could be a permission thing. Are both sql service account and sql agent account admins on the box and have access to wherever you are getting the XML? If CLR then what security model are you using for the assembly? If it is Asymmetric Key then look at the permissions of the login associated with that key.

Be One with the Optimizer
TG
Go to Top of Page

selphie_cat
Starting Member

4 Posts

Posted - 2013-04-16 : 06:41:38
Thanks James. I've tried doing a replace with no luck. TG the job owner is myself and I'm set up as a sysadmin. The way I'm getting the XML from a webpage is using OLE Automation Objects. I've tried so many different ways of fixing the issue that at this point I'm going to have to rethink the way we are doing this. Approach the entire problem from a different angle.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-16 : 08:17:04
Are you able to do the first step of inserting the data into a nvarchar(max) column when running from the agent?
Go to Top of Page

selphie_cat
Starting Member

4 Posts

Posted - 2013-04-16 : 08:38:36
quote:
Originally posted by James K

Are you able to do the first step of inserting the data into a nvarchar(max) column when running from the agent?



I can yes. It's when I'm taking the data from the nvarchar(max) columnand and putting into an XML Column that it fails in the agent.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-16 : 09:13:24
quote:
I can yes. It's when I'm taking the data from the nvarchar(max) columnand and putting into an XML Column that it fails in the agent.

And, if I understood your original posting correctly you are able to take the data from the nvarchar(max) column and insert into the XML column without any errors when you do it from an SSMS window, but when you try to run the same thing from SQL Server Agent, it complains?

Can you post the command you are using to move the data from nvarchar(max) table to XML table? You could try to make the successful SSMS code into a stored procedure and then try to run that. I don't have any thoughts on why a piece of code that runs correctly from SSMS does not run when run from the SQL Agent, assuming the account used to run the agent has the right permissions, so I am really grasping at straws here.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-16 : 10:12:32
Another thing you can do to help diagnose where the problem is is to save off the varchar(max) value from SSMS run and from job run and compare to see if they match or not. If not then what are the differences.

Be One with the Optimizer
TG
Go to Top of Page

selphie_cat
Starting Member

4 Posts

Posted - 2013-04-19 : 06:25:06

quote:
Can you post the command you are using to move the data from nvarchar(max) table to XML table?



Here's the conversion code. Sorry I haven't been able to update in a few days.

INSERT #XMLData(XMLD)
SELECT CAST(TEXT AS XML) As XMLData FROM #TextData
Go to Top of Page
   

- Advertisement -