BizTalk: Unable to switch the encoding

After posting about “Using a SQL Adapter to update an XML datatype column”, I ended by getting an error by the SQL Adapter when I integrated this prototype into a real world application.

 

Error:
A message sent to adapter “SQL” on send port “FileExceptions_SQL” with URI “SQL://<machineName>/<DatabaseName>/” is suspended.

 

Error details: HRESULT=”0×80040e14″ Description=”XML parsing: line 1, character 38, unable to switch the encoding“

 

 

 

 

I believe this isn’t a specific issue related to BizTalk but more of a SQL methodology of storing xml messages. In SQL Server 2005, when the data is stored in the database it is converted to UTF-16, and when it is returned from the database it is also returned as UTF-16. SQL can receive many different encodings, but will always store and return UTF-16. There is no loss of data converting UTF-8 to UTF-16.

 

After investigating on the issue, I realised that it was exactly what I was doing. My message was  UTF-8 encoded while SQL Server has a preference for UTF-16.

 

 

  <?xml version=”1.0″ encoding=”utf-8″ ?>

  <FileException> My exception message </ FileException>

 

I had two options to resolve this..

 

1) Remove the xml declaration that specifies the encoding (utf-8 )

Hmm .. doing this sounds like inviting more problems. I’m not sure if SQL Server requires this tag to be sent to it – “somthing internally”. I decided of not going down this path.

 

2) Replace the utf-8 tag to utf-16

Sounds good because this is exactly what SQL Server is demanding me to do. (Give the dog a bone J) .. I went down this option as it was no big deal covering it in one extra expression statement in my orchestration.

 

Here is the code for it..

xmlDocument = fileExceptionMessage;

strXmlDocument = xmlDocument.OuterXml;

 

//Replacing the encoding to meet SQL needs..

strXmlDocument = strXmlDocument.Replace(“utf-8″,”utf-16″);

 

//Insert the message in the Sqlfield..

insetSqlRequest.parameters.FileException = strXmlDocument;

 

 

Happy fixing ..