Symptom
- Error: Unknown column 'name.name' in field list.
-
Not able to query tables with special characters in table name.
- When refreshing a report in Crystal Reports, that contains a table with special characters, it fails with the error:
"Unknown column 'name.name' in field list"
Environment
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
-
SAP Crystal Reports 2013
-
SAP Crystal Reports 2016
- MySQL
Reproducing the Issue
-
In Crystal Reports, create a new report connecting via an ODBC to a MySQL database.
-
Add a table that contains a special character in the name like: "Table.Table-1"
-
It then display the error: "Unknown Column 'Table.Table' in field list
Cause
-
Crystal Reports generates an SQL Query without required double quotation around the Table name, therefore the special characters causes an error.
Resolution
- The following registry key control how Crystal Reports generates the SQL Query when connecting to a data source using an ODBC connection or a Native connection.
WARNING The following resolution involves editing the Microsoft Registry. Using the MS Registry Editor incorrectly can cause serious problems. Use the MS Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322
The registry key is located in the following path:
- For Crystal Reports 2008:
HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite12.0\Crystal Reports\Database\QueryBuilder\NameBuilder
- For Crystal Reports 2011, 2013, 2016:
HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder
Create a new String value:
DoubleQuotation
Right-click it, selct Modify and add a list of drivers used by Crystal Reports separated by comma.
For example: MYODBC5, MYODBC5W, MYODBC5A
To find a name for the driver used by Crystal Reports, see KBA: 2050922
See Also
1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports
Keywords
dash, hyphen, underscore, table name , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem