KB11667: "String too long" error message is seen in the LookupfailedSQL.log during lookup table migration for non-English projects in MicroStrategy Enterprise Manager 9.x.

SYMPTOM:
The Strategy Enterprise Manager data load completes with some warning messages about lookup table migrations. The data load completes successfully, but there are some ODBC errors received during SQL execution and are recorded in the LookupFailedSQL.log file.
An example of such an error is reproduced below. Specific object names and IDs have been removed.
 
 
Note: The error here comes from an Oracle database. The appearance and wording of the error will be different for other database platforms. Typically, the error will include verbiage to the effect that a data value was too long to fit into the column's data type.
 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ORA-12899: value too large for column
"DBNAME"."IS_REP"."IS_REP_DESC" (actual: 255, maximum: 254) INSERT INTO IS_REP (IS_REP_ID, IS_PROJ_ID, IS_REP_GUID, IS_REP_NAME, IS_REP_DESC, IS_REP_LOC, EM_ACCESS_ID, EM_OWNER_ID, EM_CREATE_DATE, EM_MODIF_DATE, IS_REP_ABBREV, EM_HIDDEN_ID, EM_EXISTS_ID, EM_OWNER_NAME, EM_HIDDEN_DESC, EM_EXISTS_DESC, IS_REP_VER, IS_REPTYPE_IND, IS_REPCTYPE_IND, IS_CUBE_DEF_GUID) 
 
values (8, 1, '......', '......', '......', '......', '255', 0, TIMESTAMP ('20040430215019'), TIMESTAMP ('20040430215019'), '', 0, 1, 'ADMINISTRATOR', 'Not Hidden', 'Exists', '......')
The behavior has been observed with Enterprise Manager warehouses on Oracle and DB2 UDB databases.
 
CAUSE:
This error may occur when:
 

• The Enterprise Manager warehouse has been created with the UTF-8 character set,
• an object's description is very long,
• and the description includes characters that are part of the Unicode character set, but not part of the standard ASCII character set.

Enterprise Manager allots 254 characters to store object names and descriptions in the Enterprise Manager warehouse. The Strategy Desktop interface allows descriptions of up to 250 characters to be created. Assuming that each character takes one byte, it should be impossible to exceed the length of the data column.
 
Accented characters or characters not belonging to Western alphabets may be encoded as two, or sometimes three, bytes when using Unicode character sets. Two Unicode character sets are in common use:
 

• UTF-16, also known as double-byte, encodes all characters as two bytes (16 bits). A database created using a double-byte code page would allocate two bytes for every character in a char or varchar field: varchar(20) would allow 20 characters and occupy a maximum of 40 bytes.
• UTF-8 encodes each character as one, two or three bytes depending on its Unicode representation. Characters 0-127 are encoded as one byte, 128-1023 as two bytes, and 1024-65536 as three bytes.

Certain database platforms such as DB2, when creating a database using the UTF-8 codepage, allocate one byte per character assigned in the column definition. In these databases, varchar(20) in UTF-8 would allow a maximum of 20 single-byte characters, and would occupy no more than 20 bytes. If there are characters in a column value that require more than one byte for encoding, the available length of the column will effectively be reduced.
 
Consider an object description that is 240 characters long and contains 20 accented characters. Because the number of characters is within Desktop's limit for object descriptions, the description can be entered into the metadata. In UTF-8 encoding, this description would require 260 bytes, which exceeds Enterprise Manager's limit of 254 characters for storing object descriptions in the lookup tables. As a result, the insert statements to populate the corresponding Enterprise Manager warehouse tables will fail with an ODBC error.
 
Note: Database platforms such as SQL Server allocate varchar fields per character, not per byte. This issue should not occur against these databases.
 
 
ACTION:
An issue has been logged to alter the column datatype or increase the column length in the Strategy Enterprise Manager warehouse for the required columns of all the lookup tables.
 
 
 
WORKAROUND:
Users on an Oracle database can apply the workaround of altering the column length to 255 characters (instead of 255 bytes) as shown below. The following SQL can be applied on the column/table being referred to, in the error message. In this example the IS_REP_DESC column from the IS_REP table is being used.
 
Sample Code/Error
ALTER TABLE IS_REP
MODIFY
(
IS_REP_DESC VARCHAR2(255 CHAR)
);
 
Users on a DB2 UDB database can apply the workaround of increasing the column length.  The following SQL can be applied on the column/table being referred to, in the error message. In this example the IS_REP_DESC column from the IS_REP table is being used.
 
Sample Code/Error
ALTER TABLE IS_REP ALTER COLUMN IS_REP_DESC SET DATA TYPE VARCHAR (510);
 
 
 
Other options that users can consider:

• Identify object descriptions that are causing the Enterprise Manager data load to fail, and shorten them.
• Transfer the Enterprise Manager warehouse to a new database that has been created using UTF-16 (double-byte). This would increase the amount of disk storage required.

Contact Strategy Technical Support for further options.