SAP Knowledge Base Article - Public

2050922 - How to remove the quotation marks in the SQL Query generated by Crystal Reports?

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 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off any relational database.
      
  2. Add at least one field on the report.
          
  3. 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     
    1. Open the Microsoft Registry Editor.  ( In MS Windows, under the menu "Start", select "Run", and type: regedit )
           
    2. In the Microsoft Registry Editor, navigate to the path corresponding to the version of the product used:
            
      • Crystal Reports 2008:
            
        • 32bit version of MS Windows:
          HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\Database\QueryBuilder\NameBuilder
              
        • 64bit version of MS Windows:
           HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\Database\QueryBuilder\NameBuilder
           
                  
              
      • Crystal Reports 2011, 2013, 2016:
          
        • 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.
           
               
    3. Add the String value: NoQuotes
         
    4. 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?
    1. Enable crlogger trace for Crystal Reports by following the instructions in the SAP Knowledge Base Article 1603398
           
    2. Restart Crystal Reports and create a report using the same database connection has the report uses.
          
    3. Refresh the report.
           
    4. In a Text Editor, open the crlogger trace file generated. The file name
      starts with   : crw32_
      and end with: _runtime.log 
         
      For example: Tthe file name will be like: crw32_10803_2016-2-4-14-03-11-30_runtime.log
           
        
    5. 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
              
               
    6. 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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016