Unicode data and data types can be migrated to a MS SQL Server data source and pulled into a MS SQL Server DataServer schema holder. The following table defines the supported MS SQL Server data types and their maximum sizes.

Table 1. Supported Unicode data types in MS SQL Server
Data type Maximum size
NCHAR 4000 characters
NVARCHAR 4000 characters
NVARCHAR(MAX) 230-1 characters
NTEXT 230-1 characters

MS SQL Server limits the size of CHAR and VARCHAR data types to 8000 bytes. When server data is represented by a single-byte ANSI code page, CHAR and VARCHAR data types can store 8000 characters. When SQL Server stores UCS-2 data in double-byte format, it restricts CHAR and VARCHAR data types to only 4000 characters. Therefore, when a non-Unicode OpenEdge database is migrated to MS SQL Server 2005 (or greater), the Maximum Varchar Length threshold is set to 8000 by default. Character data sized smaller than this threshold (which the user can configure lower) is converted to the VARCHAR native type. Character columns with widths greater than 8000 characters continue to be converted to the TEXT data type, MS SQL Server's legacy CLOB equivalent data type. However, when a Unicode OpenEdge database is migrated to MS SQL Server 2005 (or greater), the Maximum Varchar Length threshold is set to 4000 by default and cannot be set any higher. Character columns with widths greater than 4000 characters are converted to the NVARCHAR(max) data type (MS SQL Server's Unicode CLOB-equivalent data type).

Note:

Progress recommends using the DBTOOL utility and the Adjust Field Width option when migrating an OpenEdge database with Unicode data to MS SQL Server. Prior to performing the migration, use the database administration utility, DBTOOL, to size your character columns to store your Unicode data. The DBTOOL utility has the ability to count the number of Unicode characters and make the required width adjustment for sizing columns on the foreign data source.

For more information on DBTOOL utility, see OpenEdge Data Management: Database Administration. For more information on the For Field widths use migration option, see Table 2 and review Column width.