Symptom
- Quotations marks surrounds database field in the SQL Query.
- By default, Crystal Reports generates an SQL Query against the database using quotation marks around the database fields.
- How to remove the quotation marks in the SQL Query generated by Crystal Reports?
Environment
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
Reproducing the Issue
- In Crystal Reports, create a report off any relational database.
- Add at least one field on the report.
- When the report is refreshed, it sends an SQL Query in which each database fields are surrounded by quotations marks like:
SELECT "<Table Alias name>"."<Column name>", "<Table Alias name>"."<Column name>"
FROM "<DB name>"."<DB Schema Name>"."<Table Name>" "<Table Alias name>"
How to remove the quotation marks from the SQL Query generated by Crystal Reports?
Resolution
- To control how Crystal Reports generates the SQL Query, and remove the quotations around the database fields, use the registry key: NoQuotes
WARNING The following resolution involves editing the Microsoft Registry. Using the Microsoft Registry Editor incorrectly can cause serious problems. Use the Microsoft Registry Editor at your own risk. For more information see the SAP Knowledge Base Article 1323322
-
Open the Microsoft Registry Editor. ( In MS Windows, under the menu "Start", select "Run", and type: regedit )
-
In the Microsoft Registry Editor, navigate to the following path:
- 32bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder
- 64bit version of MS Windows:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder
- Notes: In most cases, the keys: QueryBuilder, and NameBuilder will not be present. You will need to add the keys manually to the path.
-
Add the String value: NoQuotes
-
Edit the value of: NoQuotes, and enter the name of the database driver used by the report to connect to your database.
Note:
- If there is multiple data sources you want to remove the quotations, separate each driver name by a comma, like: msodbcsql13, sqlncli10
- To know the name of the specific database driver the report uses, follow the steps below.
- How to know the name of the specific database driver the report uses?
- Enable crlogger trace for Crystal Reports by following the instructions in the SAP Knowledge Base Article 1603398
- Restart Crystal Reports and create a report using the same database connection has the report uses.
- Refresh the report.
- In a Text Editor, open the crlogger trace file generated. The file name
starts with : crw32_
and end with: _runtime.log
For example: The file name will be like: crw32_10803_2016-2-4-14-03-11-30_runtime.log
- Search for the keyword: "Query Targets:"
And on the right side, it will display the name of the driver used to connect to the data source.
For example: The log shows the line:
2016-6-27-15-49-23 10804 .\DbQueryBuilder.cpp 528 Query Targets: msodbcsql13, ODBC3Default 10
Therefore the driver used to connect to the data source in the above example, is: msodbcsql13
- Add the driver name to the registry key: NoQuotes
For example: If we use the example above, the driver name to add to the registry key NoQuotes, will be: msodbcsql13
Keywords
CR, QueryBuilder , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020