SAP Knowledge Base Article - Public

3490954 - Long text truncated to 254 characters when connecting via ODBC to Snowflake in Crystal Reports

Symptom

  • Large text truncated.
  • Snowflake Memo field truncated in Crystal Reports.
  • When using a Command Object to Snowflake, long string of text are truncated in Crystal Reports.
  • When connecting via ODBC to Snowflake, large text only display the first 254 characters in Crystal Reports.

Environment

  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
       
  • Snowflake 
  • Snowflake ODBC driver

Reproducing the Issue

  1. In Microsoft ODBC Data source Administrator, create an ODBC DSN to Snowflake.
  2. In Crystal Reports, create a report based on an ODBC connection to Snowflake.
  3. Double click on "Add Command", and enter a custom SQL Query that returns database fields with more than 254 characters.
  4. Add the database fields to the report.
  5. When refreshing the report, notice that all database fields containing large amount of text are truncated to 254 characters.

Cause

  • When using an ODBC connection to Snowflake, the database fields definition is coming from the Snowflake ODBC driver, and for large string fields is returning 254 characters, and this why large text are truncated.

Resolution

  • Set the Snowflake ODBC driver option: MapToLongVarChar, to the value: 254
  • Below are the general steps on how to do so, but for more information, see the Snowflake database documentation:

    WARNING: The following resolution involves editing the registry. Using the Microsoft Registry Editor incorrectly can cause serious problems. Use the Registry Editor at your own risk. For further information see the SAP Knowledge Base Article 1323322      

    1. Open the Microsoft Registry Editor ( Regedit )
        
    2. In the Microsoft Registry Editor, navigate to the following path:

      • For Crystal Reports 2016: ( 32bit Snowflake ODBC driver )
        HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Snowflake\Driver 
           
      • For Crystal Reports 2020: ( 64bit Snowflake ODBC driver )
        HKEY_LOCAL_MACHINE\SOFTWARE\Snowflake\Driver 
          
    3. Add the registry key: MapToLongVarChar
        
    4. Set the value to: 254

Keywords

cut, short, incorrect length , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2016 ; SAP Crystal Reports 2020