4

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:

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

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

EDIT 1: The code for obtaining the XML is as follows:

Select @url = 'http://....'

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 #TextData(HTML)
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 
4
  • Set up the SQL Profiler to show begin and end events for statements inside of a stored procedure. That should help you narrow the problem down to a single statement inside of a line. My shoot-from-the-hip thought is that there is something wrong with the quoting on the string. Apr 15, 2013 at 19:02
  • "The code takes a very long XML string in UTF-8 encoding and puts it into a single nvarchar(max) cell." -- please show us the code that does this. From the first error message, you may have an escaping issue.
    – Jon Seigel
    Apr 16, 2013 at 2:23
  • Thanks @Darin, I've setup the Profiler and I'm having a look at it now. It's not giving a lot of information however. I've probably setup the Trace incorrectly (my first time using SQL Profiler). I'll keep trying though until I figure out how to at very least use Profiler correctly. Apr 16, 2013 at 10:53
  • @JonSeigel I've put the code for retrieving the XML string above. Apr 16, 2013 at 10:53

1 Answer 1

2

line 1, character 38, unable to switch the encoding

I ran in to this a while back sql server wants the source string type to match the utf code.

    SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO

You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.

line 9, character 9, unexpected end of input

I would run this problem from a different direction.

  1. have the procedure output the received xml
  2. check for permissions issues with the service accounts (difference between sql server and agent services)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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