SAP Knowledge Base Article - Public

1422831 - How to link MS SQL Server Tables on fields defined as VARCHAR(MAX) or NVARCHAR(MAX) in Crystal Reports?

Symptom

  • MS SQL Server Database Fields are defined as VARCHAR(MAX) or NVARCHAR(MAX) 
  • When creating a report based on an ODBC connection to MS SQL Server, and trying to link the tables in Crystal Reports, the Database Linking option of any VARCHAR(MAX) and NVARCHAR(MAX) fields are grayed out or not available.
  • How to link MS SQL Server Tables on fields defined as VARCHAR(MAX) or NVARCHAR(MAX) in Crystal Reports?

Environment

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

  • MS SQL Server 2012
  • MS SQL Server 2014
  • MS SQL Server 2016
  • MS SQL Server 2019

Cause

  • VARCHAR(MAX) and NVARCHAR(MAX) fields are mapped to Memo field type, which cannot be used to join or link Tables.

Resolution

  • To be able to link the Tables on MS SQL Server Fields defined as VARCHAR(MAX) or NVARCHAR(MAX). ass the registry key: MapMaxTypeToString:

    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 2013, 2016:
        HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\ODBC
          
      • For Crystal Reports 2020:
        HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\ODBC
           
        Note: If the end of the path does not exist, add the directory.
             
    3. Add a DWORD (32bit) key, and name it: MapMaxTypeToString
        
    4. Set the value to a number between 0 and 65534 (decimal).

      Notes:
      - This specifies the string size of the VARCHAR(MAX) and NVARCHAR(MAX) fields.
      - If the key is not set or set to 0, the ODBC driver will map VARCHAR(MAX) and NVARCHAR(MAX) fields to Memo type, which can not be used to join/link.
        
    5. Re-start Crystal Reports. 
        
      Note: If the registry key is added on the SAP Crystal Server, or the SAP BusinessObjects Business Intelligence platform server, restart the Server Intelligence Agent (SIA) for the change to take effect.

Keywords

CR, VARCHAR(MAX), NVARCHAR(MAX), linking , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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