1217820 - Registry settings to configure how the SQL Query is generated in Crystal Reports

• How to change how the SQL Query is generated in Crystal Reports?
• What registry key settings are available to configure how the SQL Query is generated when connecting to a data source via an ODBC or Native connection in Crystal Reports?
    
    
• Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

• In Crystal Reports, you can modify how the syntax of the SQL Query is generated for: Query Join, Outer Join, Boolean Values, Date Time, Stored Procedure Call, Unicode Conversion, ... by adding the ODBC Driver Name in the registry key corresponding to the syntax you want to change. 
        
  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. In the List of Registry Keys Table located at the end of this article, find the registry key that can change the desired SQL Query syntax.
        
2. Open the Microsoft ODBC Data Source Administrator.
        
3. Under the tab "System", find the ODBC DSN used to connect to the database, and under the column "Driver," take note of the ODBC Driver name. 
    
    
      
      
4. Under the tab "Drivers", find the ODBC Driver Name, and scroll to the right to take note of the File name. ( ODBC Driver File Name ) 
   Note: This is the ODBC Driver File name you will need to add to the registry key corresponding to the syntax you want to change.
     
     
       
         
5. Open the Microsoft Registry Editor. ( regedit )
      
6. Navigate to the Registry Key path corresponding to the syntax you want to change.
   Note: The end of the path will most likely not exist. If it is the case, add the additional keys.
       
   For example navigate to: HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder 
            
7. Add a String Value and enter the Registry Key Name corresponding to the syntax you want to change.   
   
   For example: Add the registry key: SQLServerJoinBuilder  
          
8. Add to the Registry Key, the ODBC Driver File Name you took note of in step 4. ( without the .DLL extension )
   Note: If there are multiple ODBC Driver File Name, separate each of the driver name by a comma.
   
   For example: Add the ODBC Driver File Name: SQLNCLI11   ( This is the MS SQL Server Native ODBC Driver 11 File Name )
     
9. Close the Microsoft Registry Editor.
     
10. Start Crystal Reports, and when creating, or refreshing a report, the syntax generated for the SQL Query will change according to the registry key added.
      
        
      
• The table below list all the registry keys that control how Crystal Reports generates the SQL Query when connecting to a data source using an ODBC connection or a Native connection.      
              

  Query Builder
  Generates Tables JOIN syntax.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\JoinBuilder
  Registry Keys	For drivers that support
  SQLServerJoinBuilder	SQL Server ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b)
  InformixJoinBuilder	Informix join syntax ( FROM A, OUTER B WHERE A.a = B.b)
  OracleJoinBuilder	Oracle join syntax ( FROM A, B WHERE A.a(+) = B.b)
  OracleJoinBuilder2	Oracle ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join
  SybaseJoinBuilder	Sybase ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b) except the FULL OUTER join.
  DB2JoinBuilder	DB2 ANSI join syntax (FROM A LEFT OUTER B ON A.a = B.b)
  LotusNotesJoinBuilder	Lotus Notes join syntax (inner joins: FROM A, B WHERE A.a = B.b - left outer joins: FROM A LEFT OUTER B ON A.a = B.b - right outer joins: FROM B LEFT OUTER A ON A.a = B.b)
  StarEqualJoinBuilder	Sybase Star-Equal join syntax (FROM A, B WHERE A.a *= B.b)
  Valid values beside ODBC driver names	NativeSQLServer, ODBC3SQLServer, NativeSQLServer65, ODBC3SQLServer65, NativeOracle, ODBC3Oracle, ODBC3Oracle9, NativeOracle9, ODBC3Oracle10, NativeOracle10, NativeDB2, ODBC3DB2, NativeLotusNotes, ODBC3LotusNotes, ADODefault, ADODOTNET, ODBC2Default, ODBC3Default, ODBC3PostgreSQL, ODBC3MySQL, JDBCOracle, JDBCOracle9, JDBCOracle10, JDBCTeraData, JDBCLiquidData
  Name Builder
  Generates the syntax for specific naming conventions of ODBC drivers.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder
  Registry Keys	For drivers that support
  SQLServerNameBuilder	SQL Server naming conventions ("<StoredProcedureName>";<Number>)
  InformixNameBuilder	Informix naming conventions (Table qualifier appears at the beginning of the fully qualified table name)
  OracleNameBuilder	Oracle naming conventions ("<Package Name>"."<Stored Procedure Name>")
  SybaseNameBuilder	Sybase naming conventions.
  AccessNameBuilder	Access naming conventions (Quotations are allowed in table names and kept as they are)
  DB2NameBuilder	DB2 naming conventions.
  DoubleQuotation	Use double quotations to quote names. ( " " )
  BackQuotation	Use back quotations to quote names. ( ` ` )
  SquareBracket	Use square brackets to quote names. ( [ ] )
  Dot	Use dot as qualifier separator character. ( . )
  AtSign	Use At sign as qualifier separator character. ( @ )
  Colon	Use colon as qualifier separator character. ( : )
  Value Builder
  Generates the syntax for specific unicode drivers as well as other syntax related to Boolean values, date-time and wild card search characters of ODBC drivers.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\ValueBuilder
  Registry Keys	For drivers that support
  SQLServerValueBuilder	SQL Server value building conventions (Unicode strings prefixed with "N")
  SQLServer65ValueBuilder	SQL Server 6.5 value building conventions (Unicode strings are not prefixed with "N")
  InformixValueBuilder	Informix value building conventions (Boolean values are 'T' and 'F')
  OracleValueBuilder	Oracle value building conventions (Using TO_DATE function to convert date to string)
  OracleValueBuilder2	Oracle value building conventions (Unicode strings are prefixed with "N")
  SybaseValueBuilder	Sybase value building conventions (Unicode strings are prefixed with "N")
  AccessValueBuilder	Access value building conventions (Boolean values are 'TRUE' and 'FALSE')
  DAOAccessValueBuilder	DAO Access value building conventions (Wild cards are * and ?)
  DB2ValueBuilder	DB2 value building conventions.
  Outer Join Escape Sequence
  Generates the syntax for ODBC drivers using the outer join syntax in its SQL syntax.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq
  Registry Keys	For drivers
  MSOuterJoinEscSeq	That support Microsoft ODBC {oj ..} join escape sequence.
  NoOuterJoinEscSeq	That does not support any join escape sequence.
  Date Time Escape Sequence
  Generates the syntax for ODBC drivers using date time identifiers like {d"} for date.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\DateTimeEscSeq
  Registry Keys	For drivers that support
  MSDateTimeEscSeq1	Microsoft ODBC {ts ..}, {d ..} and {t ..} date-time escape sequences.
  MSDateTimeEscSeq2	Microsoft Access # .. # date-time escape sequence.
  Stored Procedure Clause Builder
  Generates the syntax for calling stored procedures based on the ODBC drivers in use.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\StoredProcedureClauseBuilder
  Registry Keys	For drivers that support
  DefaultStoredProcedureClauseBuilder	Default procedure calling syntax: {CALL <procedure name>(<parameters list>)}
  SQLServerStoredProcedureClauseBuilder	Microsoft SQL Server <procedure name><parameters list> procedure calling syntax.
  InformixStoredProcedureClauseBuilder	Informix EXECUTE PROCEDURE <procedure name>(<parameters list>) calling syntax.
  OracleStoredProcedureClauseBuilder	Oracle BEGIN <procedure name>(<parameters list>) END calling syntax.
  SybaseStoredProcedureClauseBuilder	Sybase EXEC <procedure name><parameters list> procedure calling syntax.
  AccessStoredProcedureClauseBuilder	Microsoft Access stored procedure calling syntax.
  DB2StoredProcedureClauseBuilder	DB2 stored procedure calling syntax.
  PSFTStoredProcedureClauseBuilder	PeopleSoft stored procedure calling syntax.
  Always Unicode Conversion
  Used for ODBC Drivers which require Unicode strings to be prefixed with "N".
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\UnicodeConversionOptions
  Registry Key	For drivers that support
  AlwaysUnicodeConversion	Unicode strings to be prefixed with "N".
  Sybase Where Clause Builder
  Used for Sybase ODBC Drivers which require the Sybase 'where' clause.
  Registry path	HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\WhereClauseBuilder
  Registry Key	For drivers that support
  SybaseWhereClauseBuilder	Sybase 'where' clause building conventions.

      

QUERY BUILDER QUERYBUILDER REGISTRY SETTINGS KEY SUBKEY LIST DRIVERS ODBC Crystal Reports Query Builder registry settings Registry keys , c2016359, Unknown column '...' in field list , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020