"An illegal character has been found in the statement. ODBC ?1 error" or "Incorrect Syntax Error", or "Database Library Error" occurs when I run a report. The error seems to involve quotes in the sql. What is the problem?

Document ID : KB000028199
Last Modified Date : 14/02/2018
Show Technical Document Details

In versions of Reporter prior to 7.0.009, the setting "Use Quoted Identifiers for Table and Column Names in Full Load" (found in the metadata manager under edit/options) was not being used. This was corrected in 7.0.009.

Certain odbc drivers return a space character rather than a valid quote character. Because of this fix, Reporter 7.0.009 ignores a response of a space and defaults to the registry value in the key for QuotedIdentifierCharacter. Reporter 7.0.008 heeds the space, somewhat questionably, since a space is obviously not a quote character. This can cause this error to occur in 7.0.009 for a report that ran fine in 7.0.008.

To take care of this situation, Go to Start/Run and type regedit. Browse to the HKEY_CurrentUser/Software/ComputerAssociates/IQObject.ini/QueryGeneration folder.

If you DO NOT want quoted identifiers, set the QuotedIdentifiers and InternalQuotedIdentifiers keys to 0 for false.

If you DO want quoted identifiers, leave these settings at 1, and change QuotedIdentifierCharacter to the appropriate value: 96 is the default (single quote), 34 is double quote, and 32 is a space. Placing a 32 in this registry setting will make Reporter behave just like 7.0.008 did.