1256215 - Text field truncates when characters are greater than 255 [NVarchar (Max)] in SQL 2005.

• Adding a text field which has characters greater than 255 [NVarchar (Max)] truncates the text field.
• This happens when the database is migrated from SQL 2000 to SQL 2005 and the structures are rescripted.

• Crystal Reports XI Release 2
• Microsoft SQL 2000 and SQL 2005
• Windows XP

SQL 2005 ODBC NVarchar(Max) is detected as Char (This version does not work).

1. Change Database Driver 

Changing the connection from ODBC to OLEDB resolves this issue. The data types for SQL 2000 and 2005 work as follows:

• SQL 2000 ODBC NVarchar(Max) is detected as Long Text (This version works correctly) when running the report.
• SQL 2005 ODBC NVarchar(Max) is detected as Char (This version does not work )
• SQL 2005 OLEDB NVarchar(Max) is detected as Long Text (This version works).
• SQL 2005 (ODBC) detects NVarchar (Max) as Char, it does not work since NVarchar (Max) should be recognized as Long Text for it to work.

2. Update Registry Key 

***WARNING***: The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems. Use the Registry Editor at your own risk. Refer to Note 1323322 for more information. 

There is a new registry key now available to allow longer string values and linking on these field types:

There is a key named MapMaxTypeToString can be set at the following location in the registry: Software\Business Objects\Suit 12.0\Crystal Reports\Database\ODBC

MapMaxTypeToString

Used to map the VARCHAR(MAX) and NVARCHAR(MAX) to String type, fill the size here, but limited to 65534.

Note: If the key is not set or set to 0, the ODBC driver will map it to Memo, which cannot be used to join/link.

Text truncates characters, MapMaxTypeToString, NVarchar (Max) , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports XI R2