Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Data Type Details : Data type conversion details
 

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 70. MS SQL Server data type details
MS SQL Server data type
SQL-ODBC data type1
OpenEdge-equivalent data types
integer
SQL_INTEGER
INTEGER2
(DECIMAL)
(INT64)3
bigint
SQL_BIGINT
INT644
smallint
SQL_SMALLINT
INTEGER
(DECIMAL or LOGICAL)
tinyint
SQL_TINYINT
INTEGER
(DECIMAL or LOGICAL)
decimal
SQL_DECIMAL
DECIMAL
(INTEGER)
(INT64)5
numeric6
SQL_DECIMAL
DECIMAL
(INTEGER)
(INT64)7
float8
SQL_FLOAT
DECIMAL
(INTEGER)
(INT64)9
double precision
SQL_DOUBLE
DECIMAL
(INTEGER)
(INT64)10
real
SQL_REAL
DECIMAL
(INTEGER)
(INT64)11
char12,13
SQL_CHAR
CHARACTER
nchar14,15
SQL_NCHAR
CHARACTER
varchar16,17
SQL_VARCHAR
CHARACTER
varchar(max)
SQL_VARCHAR
SQL_LONGVARCHAR
CHARACTER
CLOB
nvarchar18
SQL_NVARCHAR
CHARACTER
text19
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
CHARACTER20
(DATE)21
smalldatetime
SQL_TIMESTAMP
CHARACTER22
(DATE)23
binary24
SQL_BINARY
CHARACTER
varbinary
SQL_VARBINARY
CHARACTER
varbinary(max)
SQL_VARBINARY
SQL_LONGVARBINARY
CHARACTER
BLOB
image
SQL_LONGVARBINARY
CHARACTER25
BLOB
bit
SQL_BIT
LOGICAL
timestamp26
SQL_VARBINARY
CHARACTER
identity,27,28
Depends on underlying type
Depends on underlying type
uniqueidentifier29
SQL_VARBINARY
CHARACTER
compounded columns30
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 Numeric expressions supported include DECIMAL, INTEGER, and INT64.

6 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.

7 Numeric expressions supported include DECIMAL, INTEGER, and INT64.

8 Do not use the FLOAT or REAL data types in joins, in primary keys, or with the equality operator.

9 Numeric expressions supported include DECIMAL, INTEGER, and INT64.

10 Numeric expressions supported include DECIMAL, INTEGER, and INT64.

11 Numeric expressions supported include DECIMAL, INTEGER, and INT64.

12 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.

13 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.

14 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.

15 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.

16 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.

17 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.

18 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.

19 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.

20 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).

21 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.

22 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.

23 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.

24 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.

25 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.

26 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.

27 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.

28 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.

29 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.

30 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.