Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
SSRS EXPERT 41
BI 36
#BI 33
brh 28
SSIS BEST PRACTICES 23
TSQL 19
MS BI STACKS 18
SSRS 17

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

SSRS #45 - There is an error in XML document

Apr 28 2011 9:21PM by Sherry Li   

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.

The Issue

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.

clip_image002

 

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"

image

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.

image

The Solution

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:

Tab: char(9)
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.

image

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.

Tags: SSRS, xml, non printable character, Ascii, Char, Replace, brh,


Sherry Li
14 · 12% · 3827
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]