MS SQL Server data types differ from OpenEdge data types. However, each data type supported by the DataServer has at least one OpenEdge equivalent.

The DataServer translates MS SQL Server data types into OpenEdge equivalents and places the mapping into the schema holder. You can access this mapping information using the Data Dictionary. For example, the Data Dictionary lists the MS SQL Server datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the MS SQL Server timestamp data type. There can also be some restrictions in data source compatibility with OpenEdge.

You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the MS SQL Server tinyint data type to the OpenEdge equivalent, INTEGER. Suppose, however, that your application uses the tinyint field in such a way that the LOGICAL data type is a more suitable equivalent. In this case, you would change the data type from INTEGER to LOGICAL in the schema holder. If you do change a data type mapping, be sure to select a data type that accommodates the data in the column, otherwise conversion errors might occur at run time. Also, remember to specify a display format that is appropriate for the new data type. See Modifying a schema holder for an explanation of how to use the Data Dictionary to change OpenEdge data types in the schema holder.

The table that follows lists these data types and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your MS SQL Server data source. See Data Type Details for more details about each MS SQL Server data type, its ODBC SQL equivalent, and OpenEdge data type equivalent.

Table 1. MS SQL Server data type equivalents in OpenEdge
MS SQL Server data type OpenEdge data type equivalent1
integer INTEGER

(DECIMAL)

(LOGICAL)

(INT64)
bigint INT64

(DECIMAL)

(INTEGER)
smallint INTEGER

(DECIMAL)

(LOGICAL)

(INT64)
tinyint INTEGER

(DECIMAL)

(LOGICAL)

(INT64)
decimal DECIMAL

(INTEGER)

(INT64)
numeric DECIMAL

(INTEGER)

(INT64)
float DECIMAL

(INTEGER)

(INT64)

double precision DECIMAL

(INTEGER)

(INT64)
real DECIMAL

(INTEGER)

(INT64)
char CHARACTER2
nchar CHARACTER2
varchar CHARACTER2
nvarchar CHARACTER2
text 3 CHARACTER2

CLOB4

varchar(max) CHARACTER2, 5

CLOB4
ntext3 CHARACTER2

CLOB4
nvarchar(max) CHARACTER2,5

CLOB4
money DECIMAL

(INTEGER)
smallmoney DECIMAL

(INTEGER)
date5 DATE

(DATETIME)

(DATETIME-TZ)

(CHARACTER)2
time5 CHARACTER2

(DATETIME )
datetime DATE

(CHARACTER)2
datetime25 DATETIME

(DATE)

(DATETIME-TZ)

(CHARACTER)2
smalldatetime DATE

(CHARACTER)2
datetimeoffset5 DATETIME-TZ

(DATETIME)

(DATE)

(CHARACTER)2
binary CHARACTER2
varbinary CHARACTER2
varbinary(max) 6 CHARACTER2, 5

BLOB7
image3 CHARACTER2 BLOB7
bit LOGICAL
timestamp CHARACTER8, 2
identity 9 Depends on underlying type8
uniqueidentifier CHARACTER2, 8
computed columns Depends on underlying type8
cursor Not supported10
sql_variant Not supported10
geometry Not supported10
geography Not supported10
hierarchyid Not supported10
raw Not supported10

1 The initial entry identifies the default data type. Data types in parentheses identify supported options.
2 The value of this datatype changes, during migration, based on the settings of "_USE_OE_INIT_VALUE" or "_BLANKDEFAULT" environment variables. For more information, refer to Table 2
3 Starting in MS SQL Server 2005, legacy "image" and "text" LOB types on the server can be expressed as BLOB and CLOB types respectively. While still supported, Progress recommends legacy LOB types be converted to varbinary(max) and varchar(max) types for better forward compatibility.
4 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge CLOB by selecting the CLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS character LOB types to OpenEdge CLOBs for better scalability.
5 Starting in MS SQL Server 2008 using native drivers, these data types can be pulled into an OpenEdge schema holder for MS SQL Server.
6 The FILESTREAM variant of varbinary(max) data type is also supported.
7 The default CHARACTER mapping for binary LOB server types can be changed to OpenEdge BLOB by selecting the BLOBs checkbox on the Default to OpenEdge LOB for option during object selection on schema pull. Changing the mapping from OpenEdge character to OpenEdge LOB may require code changes. However, Progress recommends mapping MSS binary LOB types to OpenEdge BLOBs for better scalability.
8 These non-updatable columns can be mapped into an MSS DataServer schema holder but cannot be written to on the server.
9 You can add an identity data type to a table if you have not already set the PROGRESS_RECID column to the table's identity column. You can only use the PROGRESS_RECID identity column if you want to set an identity column for ROWID designation in your OpenEdge file schema. identity columns created by the user cannot be designated for OpenEdge ROWID or used as part of an index composite that designates ROWID.
10 For more information on unsupported data types, see Working with unsupported data types.