Hi guys,

I ran into a strange bug in Visual Studio when I was developing a new report to track the effort of each team member. Noticed that “description” field on the incidents was containing an invalid ASCII symbol,called “VT”. Imagine field “description” as a field available on a random ITIL platform where the users enters the full incident description.

Please find below, the steps I took since the moment I found the error and the moment I finally realize how easy is to solve it.

The problem

Steps.

1. The report has generated fine as well when we exported to Excel.
   The issue started when we weren’t able to open the Excel file, because Excel 2010 said it was corrupted:
   

   • Facebook
   • Google+
   • Evernote
   • LinkedIn
2. Press “Yes” and a second popup appears:
   Excel was able to open the file by repairing or removing the unreadable content.Removed content: /xl/worksheets/sheet1.xml-Part with XML error. Invalid XML character. Row 2120, column 296.
3. Ok, Sherlock let’s start. I remembered that under the hood, MS Office files are just ZIP files containing XML. So I renamed the .xlsx file to .zip and found the actual data inside, in the XML file indicated by the second error message: \xl\worksheets\sheet1.xml.
   

   • Facebook
   • Google+
   • Evernote
   • LinkedIn
4. Tried to open the file. As far as good. As all content in XML was in one single line, I searched for the 296th character in that line (because the error message said something about column 296) and found this:
   

   • Facebook
   • Google+
   • Evernote
   • LinkedIn
   
   That’s a Vertical Line. Microsoft Word uses VT as a line separator in order to distinguish it from the normal new line function, which is used as a paragraph separator.
   So that’s what caused the problem: non-printing characters in the field.

The solution

I replaced the field directly but I found a solution to be implemented on our data warehouse where is basically creating a new function that just replaced all non-printing characters (ASCII code < 32):

Script:

CREATE FUNCTION [dbo].[FormatTextfields]
(
@inputtext nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
set @inputtext = replace(@inputtext, char(1), '')
set @inputtext = replace(@inputtext, char(2), '')
set @inputtext = replace(@inputtext, char(3), '')
set @inputtext = replace(@inputtext, char(4), '')
set @inputtext = replace(@inputtext, char(5), '')
set @inputtext = replace(@inputtext, char(6), '')
set @inputtext = replace(@inputtext, char(7), '')
set @inputtext = replace(@inputtext, char(8), '')
set @inputtext = replace(@inputtext, char(9), '')
set @inputtext = replace(@inputtext, char(10), '')
set @inputtext = replace(@inputtext, char(11), '')
set @inputtext = replace(@inputtext, char(12), '')

-- replace line break by a blank space, 
-- for words that were in different 
-- lines keep the space between them
set @inputtext = replace(@inputtext, char(13), ' ')
set @inputtext = replace(@inputtext, char(14), '')
set @inputtext = replace(@inputtext, char(15), '')
set @inputtext = replace(@inputtext, char(16), '')
set @inputtext = replace(@inputtext, char(17), '')
set @inputtext = replace(@inputtext, char(18), '')
set @inputtext = replace(@inputtext, char(19), '')
set @inputtext = replace(@inputtext, char(20), '')
set @inputtext = replace(@inputtext, char(21), '')
set @inputtext = replace(@inputtext, char(22), '')
set @inputtext = replace(@inputtext, char(23), '')
set @inputtext = replace(@inputtext, char(24), '')
set @inputtext = replace(@inputtext, char(25), '')
set @inputtext = replace(@inputtext, char(26), '')
set @inputtext = replace(@inputtext, char(27), '')
set @inputtext = replace(@inputtext, char(28), '')
set @inputtext = replace(@inputtext, char(29), '')
set @inputtext = replace(@inputtext, char(30), '')
set @inputtext = replace(@inputtext, char(31), '')
return @inputtext
END

…and called it on the “SELECT” statement of the dataset something like this:

SELECT
INC.Field1,
dbo.FormatTextfields(INC.Description) AS 'Description'
FROM ITSM76_HPD_Help_Desk AS INC

We need to make sure that it’s mentioned in every report (SQL) which contains some fields that might contain non-printing characters, like description fields.

This should help you avoid this error message in the future.
Hopefully, there will be a service pack in the future that fixes this bug in SSRS 2012.

Hope you find it useful!
RMC