Microsoft KB Archive/234070

= You receive a "DataType Property for Field 'Field_Name' is Invalid" error message when you try to access remote views of SQL Server 7.0 tables in Visual FoxPro =

Article ID: 234070

Article Last Modified on 3/19/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q234070



SYMPTOMS
When you try to use a remote view of Microsoft SQL Server 7.0 tables that contains Unicode fields (NCHAR, NVARCHAR, and NTEXT) defined with a length greater than 254 characters, the following error message appears in Microsoft Visual FoxPro:

DataType Property for Field 'Field_Name' is Invalid



RESOLUTION
There are two workarounds for this issue.   Use the DBSETPROP function to set the DataType property to a valid value. The following code illustrates: =DBSETPROP('MyView.UniCodeField','Field','DataType','C(254)') NOTE: Attempting to set the DataType Property to "M" results in the following error message when attempting to use the remote view:

Type conversion required by the DataType property for field "name" is invalid

  Explicitly cast the Unicode fields, defined with a length greater than 254 characters, when creating the remote view: CREATE SQL VIEW view2 REMOTE CONNECTION uniconn ; AS SELECT cast(nchar_fld AS TEXT) AS nchar_fld, ; unitest.char_fld, cast(nvarchar_fld AS TEXT) AS nvarchar_fld, ; unitest.varchar_fld, cast(ntext_fld AS TEXT) AS ntext_fld, ; unitest.text_fld ; FROM dbo.unitest unitest 



MORE INFORMATION
Microsoft SQL Server 7.0 introduces three new Unicode datatypes, which cannot be sent to clients using DB-Library (such as ISQL) or ODBC Drivers prior to version 3.70. The new data types are:

NCHAR Fixed-length Unicode character data of n characters. N must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character. Maps to a character field. With the 3.70.0623 ODBC driver set to the default compatibility mode, these fields will display with an additional binary character.NVARCHAR Variable-length Unicode character data of n characters. N must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying. Maps to a character field.NTEXT Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text. Maps to a character field with a length of 255. Visual FoxPro remote views map these Unicode fields to character datatypes. When the Unicode field is defined with a length of 255 or more characters, the field in the remote view is mapped to a datatype of C(255). Attempts to use remote views that access UNICODE fields defined with a length of 255 or more characters results in a message that the DataType property for the field is invalid.

Steps to reproduce the behavior
Note In this code example, replace My_SQL_70_Server with the name of your SQL 7.0 Server in the connection strings of the code example and make sure that the user has the appropriate permissions.   Create a program file named "UNICODE.PRG" using the following code snippet: *!* Set up Error Handler ON ERROR DO TRAPERR WITH MESSAGE gnconnhandle=SQLSTRINGCONN("DRIVER={SQL Server};SERVER=My_SQL_70_Server;" + ;  "DATABASE=PUBS;UID=UserName;PWD=StrongPassword") IF gnconnhandle>0 SQLCommand="CREATE TABLE UNITEST (nchar_fld nchar(255), " + ;     "char_fld char(255),  nvarchar_fld nvarchar(255), " + ;      "varchar_fld varchar(255), ntext_fld ntext, text_fld text)" =sqlexec(gnconnhandle,SQLCommand) =sqldisconn(gnconnhandle) ENDIF CREATE DATABASE unitesta CREATE CONNECTION uniconn CONNSTRING "DRIVER={SQL Server};SERVER=My_SQL_70_Server;" + ; "DATABASE=PUBS;UID=UserName;PWD=StrongPassword" CREATE SQL VIEW view1 REMOTE CONNECTION uniconn ; AS SELECT * FROM dbo.unitest unitest
 * !* Create a connection to SQL Server
 * !* Create a SQL Server table with Unicode Fields
 * !* Create a FoxPro Database Container
 * !* Create a connection to SQL Server
 * !* Create a remote view using the SQL Server Table created above

CREATE SQL VIEW view2 REMOTE CONNECTION uniconn ; AS SELECT cast(nchar_fld AS TEXT) AS nchar_fld, ; unitest.char_fld, cast(nvarchar_fld AS TEXT) AS nvarchar_fld, ; unitest.varchar_fld, cast(ntext_fld AS TEXT) AS ntext_fld, ; unitest.text_fld ; FROM dbo.unitest unitest
 * !* Create a remote view using the SQL Server Table created above.
 * !* Explicit cast the datatype for Unicode fields in the remote view definition.

=DATATYPE('View1') =DATATYPE('View2')
 * !* Display information about the datatype properties of the fields in views

USE view1
 * !* Attempt to USE View1
 * !* This results in the error message "DataType Property for field 'NChar_fld' is invalid"

USE view2 browse timeout(2)
 * !* Attempt to USE View2
 * !* No Error occurs

=DBSETPROP('view1.nchar_fld','field','datatype','c(254)') =DBSETPROP('view1.nvarchar_fld','field','datatype','c(254)') =DBSETPROP('view1.ntext_fld','field','datatype','c(254)')
 * !* Set the DataType Property for the Unicode Fields, used in View1, to valid settings.

=DATATYPE('View1')

USE view1 browse timeout(2)
 * !* Attempt to USE View1
 * !* No Error occurs

USE

=DBSETPROP('view1.ntext_fld','field','datatype','M')
 * !* Set the DataType Property for the Unicode Text Field, used in View1, to M.

USE view1 CLOSE ALL ON ERROR RETURN
 * !* Attempt to USE View1
 * !* This results in the error message
 * !* "Type conversion required by the DataType Property for field 'Ntext_fld' is invalid"

PROCEDURE DATATYPE PARAMETER viewname MSGSTRING="NCHAR   " + CHR(9) + CHR(9) + DBGETPROP(viewname + '.nchar_fld','field','datatype') + CHR(13) + ; "CHAR    " + CHR(9) + CHR(9) + DBGETPROP(viewname + '.char_fld','field','datatype') + CHR(13) + ; "NVARCHAR " + CHR(9) + DBGETPROP(viewname + '.nvarchar_fld','field','datatype') + CHR(13) + ; "VARCHAR " + CHR(9) + DBGETPROP(viewname + '.varchar_fld','field','datatype') + CHR(13) + ; "NTEXT   " + CHR(9) + CHR(9) + DBGETPROP(viewname + '.ntext_fld','field','datatype') + CHR(13) + ; "TEXT    " + CHR(9) + CHR(9) +  DBGETPROP(viewname + '.text_fld','field','datatype') =MESSAGEBOX(MSGSTRING,64,'DataType Properties of '+ALLTRIM(viewname)) RETURN

PROCEDURE TRAPERR PARAMETER errormsg =MESSAGEBOX(errormsg,16,'Unicode Demonstration Error') RETURN   From the Command window, type: DO UNICODE  Note the output that displays on screen.</li> Note that attempting to USE View1 initially fails, but is successful after the DataType properties for the Unicode fields have been changed to valid settings.</li> Note that the attempt to USE View2 succeeds.</li></ol>

Keywords: kberrmsg kbdatabase kbprb KB234070

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.