Error "Cannot read the next data row for the dataset 'dataset name'" occurs when accessing an IT Analytics report
When accessing an IT Analytics report, the error "An error has occurred during report processing. Cannot read the next data row for the dataset 'dataset name'." occurs. Also, processing of cubes that relate to the report may also result in issues.
In IT Analytics reports:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset 'dataset name'. (rsErrorReadingNextDataRow)
For more information about this error navigate to the report server on the local server machine, or enable remote errors.
In SQL when trying to process a cube or dimension:
The server sent an unrecognizable response.
"<invalid-character>, hexidecimal value 0x<hex-value>, is an invalid character. Line <line-#.>, position <position-#>. (System.Xml)
If invalid XML characters (i.e., certain non-alphanumeric characters) are present in data, the Microsoft SQL Server Analysis Server (SSAS) may not be able to process cubes and their dimensions. This is because XML reserves certain characters that are non-alphanumeric in nature for use as programming code. When these are seen in data, XML attempts to process them as escape codes for code, but as they are not actually code, it results in errors. This is therefore not a limitation or issue caused by the IT Analytics product or by SSAS, but by the contents of the user's data, however it was populated. This may occur also if data becomes corrupted (SQL server crash, bad import, etc.)
Additional information on this can be found on Microsoft's web sites:
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.dataitem.invalidxmlcharacters.aspx
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.invalidxmlcharacters.aspx
http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.dataitem.invalidxmlcharacters(v=sql.90).aspx
IMPORTANT NOTES ABOUT USING THIS ARTICLE:
(1) This article is only intended to troubleshoot and resolve the exact errors as shown above. If the user sees a different variation of these errors, this article is most likely not applicable.
(2) For simplicity of reading, the affected characters that are the issue, which are invalid XML characters, are from here on referred to as "non-alphanumeric characters", as this is how they will appear to the user. This does not indicate and should not be confused with, however, that any non-alphanumeric character is an issue, but only refers to those that will affect XML, as described in the URL references in the Cause section, above.
(3) "Non-alphanumeric charactes" refers to any non-standard or invalid character found that normally shouldn't be used. For example, copyright symbols, trademark symbols, line graphics, or what appear to be oriental letters (corrupted data, in this case). All letters, numbers and standard characters such as hyphens, periods, + signs, etc., are acceptable.
(4) Symantec Technical Support is unable to assist the user in in-depth troubleshooting this issue based on the cause: their data is invalid and SSAS directly cannot process the data. ITA is thereby effected, but is not the cause of the issue in itself. The user must themselves find and resolve any bad data that contains non-alphanumeric characters. Please contact your DBA if you need assistance in doing this.
To help the user get started in troubleshooting this, Symantec Technical Support has provided the following suggestions:
Preferred Solution: Correct the data directly.
Workaround: Change SSAS to ignore the non-alphanumeric characters.
Troubleshooting: Use SQL to help find the columns and fields that contain non-alphanumeric characters.
While it is beyond the scope of Symantec Technical Support to assist in resolving data issues such as non-alphanumeric characters found in the customer's data, the attached SQL script "Parse Tables and Columns for Non-Alphanumeric Characters.sql" can help the user in finding non-alphanumeric data. This script can be ran against any database that is suspected of having non-alphanumeric characters (Symantec_CMDB, ProcessManager, SEPM, etc.) that IT Analytics accesses for data for its reports.
NOTES:
(1) This SQL script does not remove the data but only finds it for the user.
(2) Once the user determines which columns and fields contain non-alphanumeric data, it is up to the user to determine how best to resolve this. It is recommended to use the above solution and workarounds to help with this.
(3) Instructions on how to use the script are found inside of it. Please note that this script will take an extremely long time to run and it is not recommended to run against a production database, at least during production hours, as performance may drop. This is because this script will parse through every table, column and field value looking for non-alphanumeric characters. It does not change anything but only reports on what is found. For example, on a small test database, this took seven hours to run. It is therefore strongly recommended that the user is absolutely certain that this is the issue and that the above listed solution and workarounds do not work before even considering using this script.