A field with nvarchar(max) data type in MS SQL Server has a display format of x(1) with Nvarchar data type in the schema holder

Mar 13, 2014
Knowledge

Title

URL Name

P129336

Article Number

000148526

Environment

Windows 2003 Windows NT 32 Intel/Windows 2000 MS SQL DataServer OpenEdge 10.1B, 10.1C, 10.2x, 11.x MS SQL 2005, 2008

Question/Problem Description

A field with nvarchar(max) data type in MS SQL Server has a display format of x(1) with Nvarchar data type in the schema holder
The following is shown in the .df file from the schema holder for field f1 for table t1:

ADD FIELD "f1" OF "t1" AS character
FORMAT "x(1)"
INITIAL ?
POSITION 2
LENGTH 0
ORDER 5010
FOREIGN-POS 1
FOREIGN-NAME "f1"
FOREIGN-TYPE "NVARCHAR"
DSRVR-PRECISION 0
DSRVR-SCALE 0
DSRVR-LENGTH 0
DSRVR-FLDMISC 0
FIELD-MISC22 "Y"
QUOTED-NAME """f1"""
MISC-PROPERTIES ""

The schema holder is created with Unicode support from %DLC%\prolang\utf\empty4
The code page is changed to utf-8 from default iso8859-1 during the schema holder creation process
The MS SQL Server Native Client driver is used to access the SQL Server database
Use of the SQL Server driver to connect to SQL Server Database resolves the display format and data type issue in the Data Dictionary

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Defect PSC00198284/OE00165897, PSC00257301/OE00235922

Enhancement Number

Cause

The issue with the MS SQL Server Native Client driver is that when API call is made to query the field's information, it returns the field to be of type nvarchar with size 0. The DataServer handles it as nvarchar and sets its display format to be of length 1. On the other hand, the SQL Server driver returns the field to be of type ntext with a long size, therefore, the DataServe sets its display format to x(320) and handles it properly.

The same behavior is seen for Stored procedure where variable is defined as nvarchar(max) is also pulled in to the schema holder as Nvarchar data type with format x(1). A new defect OE00235922 is filed to address this issue for SQL Server Stored Procedure.

Resolution

Upgrade to OE 10.1C01 or higher.

Workaround

As a workaround use SQL Server driver to connect to MS SQL Server database

Notes

References to Written Documentation:

P129341, "Errors 6182 and 2651 during the creation of the schema holder with Unicode support"

P129342, "Errors 6182, 2651 and 142 during the insert of data on the field with unicode data type nvarchar(max)"

ee

Keyword Phrase

Last Modified Date

9/13/2015 4:20 AM

A field with nvarchar(max) data type in MS SQL Server has a display format of x(1) with Nvarchar data type in the schema holder

Files(0)

Was this article helpful?

Was this article helpful?

A field with nvarchar(max) data type in MS SQL Server has a display format of x(1) with Nvarchar data type in the schema holder

Information

Related Articles