Use Microsoft SQL Server DataServer

Change in schema

Save PDF

Table of Contents

Change in schema

Save PDF

Last Updated: January 17, 2024
2 minute read

OpenEdge
Version 12.8
Documentation

There may be differences in the schema generated with each driver for the LOB and datetime data types.

Change in Schema for LOB data types

The schema changes for the LOB data types vary while creating the schema holder if you select the Default to OpenEdge LOB for: option for CLOB or BLOB in the Pre-Selection Criteria For Schema Pull dialog box. For more details, see Creating a schema holder .

The following table identifies the LOB datatypes for which there is a difference in the schema for each driver:


Data Type	OpenEdge Wire Protocol Driver	SQL Server Driver	SQL Native Client 11 Driver	ODBC Driver 18 for SQL Server	OpenEdge Wire Protocol Driver¹	SQL Server Driver ¹	SQL Native Client 11 Driver ¹	ODBC Driver 18 for SQL Server ¹
nvarchar(max)	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	character `FOREIGN-TYPE "NVARCHAR" DSRVR-PRECISION 16000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	character `FOREIGN-TYPE "NVARCHAR" DSRVR-PRECISION 16000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NVARCHAR" DSRVR-PRECISION 16000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	clob `FOREIGN-TYPE "NVARCHAR" DSRVR-PRECISION 16000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`
varchar(max)	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "VARCHAR" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	character `FOREIGN-TYPE "VARCHAR" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "VARCHAR" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	clob `FOREIGN-TYPE "VARCHAR" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`
varbinary(max)	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "VARBINARY" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	character `FOREIGN-TYPE "VARBINARY" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "VARBINARY" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`	blob `FOREIGN-TYPE "VARBINARY" DSRVR-PRECISION 32000 DSRVR-SCALE 0 DSRVR-LENGTH 32000`
ntext	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	character `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`	clob `FOREIGN-TYPE "NLONGVARCHAR" DSRVR-PRECISION 1073741823 DSRVR-SCALE 0 DSRVR-LENGTH 1073741823`
text	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	clob `FOREIGN-TYPE "LONGVARCHAR" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`
image	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	character `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`	blob `FOREIGN-TYPE "LONGVARBINARY" DSRVR-PRECISION 2147483647 DSRVR-SCALE 0 DSRVR-LENGTH 2147483647`

Note: By checking the Default to OpenEdge LOB for: box, your ABL application is coded specifically to handle LOB objects with OpenEdge LOB operations. Select the CLOBs checkbox to map MS SQL Server VARCHAR(MAX) and TEXT data types to OpenEdge CLOBS. Select the BLOBs checkbox to map MS SQL Server VARBINARY(MAX), IMAGE and FILESTREAM data types to OpenEdge BLOBs. Otherwise, all server LOB data types map to the CHARACTER data type in OpenEdge for backward-compatibility.

CAUTION: Selecting YES enables the use of LOB objects in the ABL language for LOBs in the foreign schema, but also disables compatibility with the original OpenEdge database and causes an Adjust Schema operation to fail.

Change in Schema for Datetime data types

The schema changes for the Datetime data types vary while creating the schema holder if you select the Default to OpenEdge DATETIME option in the Pre-Selection Criteria For Schema Pull dialog box. For more details, see Creating a schema holder.

The following table identifies the date and time data types for which there is a difference in schema for each driver:


Data Type	OpenEdge Wire Protocol Driver	SQL Server Driver	SQL Native Client 11 Driver	ODBC Driver 18 for SQL Server	OpenEdge Wire Protocol Driver²	SQL Server Driver ²	SQL Native Client 11 Driver²	ODBC Driver 18 for SQL Server²
date	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`	not supported	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`	not supported	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`	date `FOREIGN-TYPE "DATE" DSRVR-PRECISION 10 DSRVR-SCALE 0 DSRVR-LENGTH 6`
datetime	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`	not supported	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`	not supported	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 23 DSRVR-SCALE 3 DSRVR-LENGTH 16`
smalldatetime	not supported	not supported	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 16 DSRVR-SCALE 0 DSRVR-LENGTH 16`	date `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 16 DSRVR-SCALE 0 DSRVR-LENGTH 16`	not supported	not supported	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 16 DSRVR-SCALE 0 DSRVR-LENGTH 1`	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 16 DSRVR-SCALE 0 DSRVR-LENGTH 16`
datetime2	not supported	not supported	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 27 DSRVR-SCALE 7 DSRVR-LENGTH 16`	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 27 DSRVR-SCALE 7 DSRVR-LENGTH 16`	not supported	not supported	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 27 DSRVR-SCALE 7 DSRVR-LENGTH 16`	datetime `FOREIGN-TYPE "TIMESTAMP" DSRVR-PRECISION 27 DSRVR-SCALE 7 DSRVR-LENGTH 16`
datetimeoffset	not supported	not supported	datetime-tz `FOREIGN-TYPE "TIMESTAMP-TZ" DSRVR-PRECISION 34 DSRVR-SCALE 7 DSRVR-LENGTH 20`	datetime-tz `FOREIGN-TYPE "TIMESTAMP-TZ" DSRVR-PRECISION 34 DSRVR-SCALE 7 DSRVR-LENGTH 20`	not supported	not supported	datetime-tz `FOREIGN-TYPE "TIMESTAMP-TZ" DSRVR-PRECISION 34 DSRVR-SCALE 7 DSRVR-LENGTH 20`	datetime-tz `FOREIGN-TYPE "TIMESTAMP-TZ" DSRVR-PRECISION 34 DSRVR-SCALE 7 DSRVR-LENGTH 20`
time	not supported	not supported	character `FOREIGN-TYPE "TIME" DSRVR-PRECISION 16 DSRVR-SCALE 7 DSRVR-LENGTH 12`	character `FOREIGN-TYPE "TIME" DSRVR-PRECISION 16 DSRVR-SCALE 7 DSRVR-LENGTH 12`	not supported	not supported	character `FOREIGN-TYPE "TIME" DSRVR-PRECISION 16 DSRVR-SCALE 7 DSRVR-LENGTH 12`	character `FOREIGN-TYPE "TIME" DSRVR-PRECISION 16 DSRVR-SCALE 7 DSRVR-LENGTH 12`

Note:

FOREIGN-TYPE—Displays the foreign datatype.
DSRVR-PRECISION—Displays the total number of digits in decimal fields or the precision of the field.
DSRVR-SCALE—Displays the digits to the right of the decimal or the scale of the field.
DSRVR-LENGTH—Displays the length of the field when represented by characters. For numbers, it defaults to the maximum limit allowed for a field.

¹ To enable the mapping of MS SQL Server LOB data types to the OpenEdge LOBs, set Default to OpenEdge LOB for to YES in the Pre-Selection Criteria For Schema Pull dialog box.

² To automatically map the MS SQL Server timestamp data types to the associated OpenEdge equivalent DATETIME data type, set Default to OpenEdge DATETIME to YES in the Pre-Selection Criteria For Schema Pull dialog box.