I’ve been working lots of hours lately because our project is just about to go into production. One of my ETL processes has gone through many changes recently because business users are now very actively sending us their late minutes requirements.
After making some changes to my ETL process, and re-loading all the historical data, I discovered that one of my SSRS reports was no longer working.
Here is the error message from the reporting server (the report has no problem running in BIDS):
There is an error in XML document (1, 6407).
'', hexadecimal value 0x16, is an invalid character. Line 1, position 6407.
This is my first time encountering this kind of error. Not knowing the root cause, I spent the next hour or so trying to “fix” it. I tried all the possible ways I could think of, such as trying to find that unprintable character in the RDL/XML file, re-loading the RDL, renaming the RDL file, etc. No luck.
The Root Cause
Next morning, I started searching on Internet, and found many info on this error. The conclusion is that this is caused by a bug on the Reporting Services 2005. A service pack needs to be installed.
But it’s this article from Microsoft that revealed the root cause of the error.
Error message when you try to browse a report in SQL Server 2005 Reporting Services: "The input XML is not well-formed"
This problem occurs because a nonprintable character is populated into a report parameter that you define in the report.
Knowing my data and report very well, it didn’t take me long to find the report parameter that has a nonprintable character in it.
I would not wait for the architecture team to apply the service pack. The Microsoft support article also gave you workaround in SSRS. I don’t want to do that either. I am a SQL developer so it’s natural for me to resolve it in SQL.
I decided to replace the non printable character by an empty space. Something like this:
REPLACE(myCOLUMNS, CHAR(9), '')
If you are in the SQL world for awhile, you know that the CHAR function with the right number (which is the ASCII value for the character) will give you special non printable characters.
Some common special non printable characters:
Line feed: char(10)
Carriage return: char(13)
What about the one in my data? What kind of non printable character it is? I tried the above three common ones, no luck.
I need to use another SQL function ASCII to tell me that. It turned that the ASCII value for it is 22.
Here comes to my final solution:
REPLACE(myCOLUMNS, CHAR(22), '')
or I could just simply use:
REPLACE(myCOLUMNS, '', '')
Final notes before I close this post:
1. The SQL functions CHAR and ASCII are similar to the Chr and Asc functions within VBScript.
2. If you want to replace ANY non-printable characters in your data, leaving only numbers and printable letters, you would need to do some programming.