x

Getting “Unable to switch encoding” error in SQLServer

I have 2 database instances of SQL Server.

When I am trying to save XML into a table for one of the databases, it is working fine but for other instance I'm getting an error:

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: XML parsing: line 1, character 39, unable to switch the encoding {prepstmnt 1671783333 UPDATE MYTable SET CreateDate = ?, ExpiryDate = ?, XMLData = ? WHERE ID = ? [params=(Timestamp) 2013-07-10 18:52:12.552, (Timestamp) 2013-07-11 06:52:12.552, (String)

more ▼

asked Jul 10, 2013 at 09:42 AM in Default

avatar image

Anshuman
0 1 1 3

Can you post the details of the error message please?

Jul 10, 2013 at 11:50 AM tomgough79

Moved error to question - @KenJ

Jul 10, 2013 at 01:30 PM Anshuman
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

What datatype is the column XMLData?

Usually this error indicates that you are trying to update a non-unicode column with unicode xml - does your xml have utf-16 encoding?

more ▼

answered Jul 11, 2013 at 11:27 PM

avatar image

Mister Magoo
2.1k 2 5 8

its having UTF-8 encoding

Jul 12, 2013 at 06:02 PM Anshuman

It looks like this was a cross-post from stackoverflow: http://webcache.googleusercontent.com/search?q=cache:dlJtfiOapWcJ:stackoverflow.com/questions/17566719/getting-unable-to-switch-encoding-error-in-sqlserver+&cd=1&hl=en&ct=clnk&gl=us

It was declared duplicate and now redirects to this similar question: http://stackoverflow.com/questions/3760788/how-to-solve-unable-to-switch-the-encoding-error-when-inserting-xml-into-sql-s

The accepted answer there agrees that it's a uft-8 to utf-16 mismatch: http://stackoverflow.com/a/3761018

MSDN says that the encoding within the XML document has to match the encoding of the string containing the XML document: http://msdn.microsoft.com/en-us/library/ms131375.aspx

Here is their example on utf mismatches that can cause problems. Both of these queries produce the "unable to switch the enconding" error:

 SELECT CONVERT(XML, '<?xml version="1.0" encoding="UTF-16"?><doc/>')
 SELECT CONVERT(XML, N'<?xml version="1.0" encoding="UTF-8"?><doc/>')
Jul 12, 2013 at 06:27 PM KenJ
(comments are locked)
10|1200 characters needed characters left

I am having 2 Database instances SQL server. For one of the instance its working fine , but for other instance its giving error . not getting why is that so

more ▼

answered Jul 12, 2013 at 06:31 PM

avatar image

Anshuman
0 1 1 3

Here is the "correct" version of the two non-valid samples from above. Do both of these break on one instance but not the other?

 SELECT CONVERT(XML, N'<?xml version="1.0" encoding="UTF-16"?><doc/>')
 SELECT CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?><doc/>')
Jul 12, 2013 at 07:01 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2188
x202

asked: Jul 10, 2013 at 09:42 AM

Seen: 14641 times

Last Updated: Jul 12, 2013 at 07:01 PM

Copyright 2017 Redgate Software. Privacy Policy