Data type conversion details

The table below identifies each supported DataServer for MS SQL Server data type, its SQL-ODBC equivalent data type, and its supported OpenEdge equivalent data types. In the OpenEdge -equivalent data types column, the initial data type identified is the default. The data types in parentheses are alternative data types that you can specify in the schema holder for your MS SQL Server data source.

Note that the SQL-ODBC data types are presented as information only; you do not need to know nor use the SQL-ODBC data types to work with the DataServer for MS SQL Server.

Table 1. MS SQL Server data type details
MS SQL Server data type SQL-ODBC data type#rfi1436299088886__fn_sqlodbc OpenEdge-equivalent data types
integer SQL_INTEGER

INTEGER#rfi1436299088886__fn_int

(DECIMAL)

(INT64)#rfi1436299088886__fn_int64
bigint SQL_BIGINT INT64#rfi1436299088886__fn_int64_2
smallint SQL_SMALLINT INTEGER

(DECIMAL or LOGICAL)
tinyint SQL_TINYINT

INTEGER

(DECIMAL or LOGICAL)
decimal SQL_DECIMAL

DECIMAL

(INTEGER)

(INT64)#rfi1436299088886__fn_int64
numeric#rfi1436299088886__fn_numeric SQL_DECIMAL

DECIMAL

(INTEGER)

(INT64)#rfi1436299088886__fn_int64
float#rfi1436299088886__fn_float SQL_FLOAT

DECIMAL

(INTEGER)

(INT64)#rfi1436299088886__fn_int64
double precision SQL_DOUBLE

DECIMAL

(INTEGER)

(INT64)#rfi1436299088886__fn_int64
real SQL_REAL

DECIMAL

(INTEGER)

(INT64)#rfi1436299088886__fn_int64
char#rfi1436299088886__fn_char1,#rfi1436299088886__fn_char2 SQL_CHAR CHARACTER
nchar#rfi1436299088886__fn_char1,#rfi1436299088886__fn_char2 SQL_NCHAR CHARACTER
varchar#rfi1436299088886__fn_char1,#rfi1436299088886__fn_char2 SQL_VARCHAR CHARACTER
varchar(max) SQL_VARCHAR

SQL_LONGVARCHAR

 CHARACTER

CLOB
nvarchar#rfi1436299088886__fn_char2 SQL_NVARCHAR CHARACTER
text#rfi1436299088886__fn_text SQL_LONGVARCHAR CHARACTER

CLOB
ntext SQL_LONGVARCHAR

CHARACTER

CLOB
nvarchar(max) SQL_VARCHAR

CHARACTER

CLOB
money SQL_DECIMAL

DECIMAL

(INTEGER)
smallmoney SQL_DECIMAL

DECIMAL

(INTEGER)
datetime SQL_TIMESTAMP

CHARACTER#rfi1436299088886__fn_char

(DATE)#rfi1436299088886__fn_date
smalldatetime SQL_TIMESTAMP

CHARACTER#rfi1436299088886__fn_date

(DATE)#rfi1436299088886__fn_date2
binary#rfi1436299088886__fn_char1 SQL_BINARY CHARACTER
varbinary SQL_VARBINARY CHARACTER
varbinary(max)

SQL_VARBINARY

SQL_LONGVARBINARY

CHARACTER

BLOB
image SQL_LONGVARBINARY

CHARACTER#rfi1436299088886__fn_date

BLOB
bit SQL_BIT LOGICAL
timestamp#rfi1436299088886__fn_date2 SQL_VARBINARY CHARACTER
identity,#rfi1436299088886__fn_date2,#rfi1436299088886__fn_ident Depends on underlying type Depends on underlying type
uniqueidentifier#rfi1436299088886__fn_date2 SQL_VARBINARY CHARACTER
compounded columns#rfi1436299088886__fn_date2 Depends on underlying type Depends on underlying type
1 The SQL-ODBC data types demonstrate the mapping of native MS SQL Server data types to the ODBC standard.
2 Existing or new 32-bit INTEGER data types have a maximum internal field size of 64-bits. The internal field expansion supports larger values defined as INTEGER data types.
3 Numeric expressions supported include DECIMAL, INTEGER, and INT64.
4 The OpenEdge INT64 data type enables the MS SQL Server DataServer BIGINT data type to be successfully pulled into a schema holder.
5 The DataServer truncates values in MS SQL Server decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only.
6 Do not use the FLOAT or REAL data types in joins, in primary keys, or with the equality operator.
7 When you define a binary or char column to allow nulls, MSS stores the data type definitions as varbinary and varchar respectively. This does not affect how the DataServer maps the MSS data types to OpenEdge data types.
8 When migrating an OpenEdge database to SQL Server, character fields whose format is less than x(8000) are defined as VARCHAR by default if "ABL format" is used for field widths during migration. Fields whose SQL widths are less than 8000 are defined as varchar by default if "width" is used for fields widths during migration, and larger character fields are defined as VARCHAR(MAX). When the database uses unicode data types, the 800 byte record limit in SQL Server must be expressed in 4000 characters, so the above limits are halved for unicode data.

If there are several large character fields in one record that each individually fall under the maximum size limits so as to be defined as varchar, but in sum, exceed the maximum record size for SQL server, it will be necessary to define the fields as a server CLOB type, such as varchar(max) in order to successfully create the records.

The default server type mapping for character data that exceeds the SQL Server record size limit is changed from Text/ntext to varchar(max)/nvarchar(max) in SQL Server 2005 and later.
9 The amount of data that can be accessed in a field defined to be CHARACTER by an OpenEdge application is limited by the maximum size of a record that can be passed to the client. The maximum record size for DataServer applications is 32,000 bytes. Keep this in mind if you decide to map server CLOB type to CHARACTER rather than CLOB. If you map server LOB data to CHARACTER data types but you don't process the data in these fields on the returned records, it is recommended that you construct field lists to exclude them from your query results.
10 By default, the initial value of a MS SQL Server datetime or smalldatetimecolumn is the Unknown value (?). The default initial values for binary and varbinary are also the Unknown value (?). The MS SQL Server datetime and smalldatetime data types contain both date and time information. The DataServer maps these to the OpenEdge DATE data type; however, you can change the DATE data type to CHARACTER in the schema holder. If you do this, remember to change the format to match the new data type. For example, if you change the data type to CHARACTER, specify a character format, such as x(26).
11 When mapping of the MS SQL Server datetime or smalldatetime data types is to the OpenEdge DATE data type, OpenEdge truncates the time portion of the date.
12 The DataServer considers this data type to be a hidden value. Its value is set by the server rather than the application. However, you can still access a MS SQL Server table that contains this type of column.
13 Identity columns are limited by SQL Server to one per table. The PROGRESS_RECID identity column can be auto-generated in a migrated table in order to represent ROWID for OpenEdge for that table. Your SQL Server table can define an alternative identity column but a user-defined identity column cannot be used like PROGRESS_RECID to represent ROWID. See ROWID function for more information.