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 (234070)

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.

1. Create a program file named "UNICODE.PRG" using the following code snippet:

   *!* Set up Error Handler
   ON ERROR DO TRAPERR WITH MESSAGE()
   *!* Create a connection to SQL Server
   gnconnhandle=SQLSTRINGCONN("DRIVER={SQL Server};SERVER=My_SQL_70_Server;" + ;
      "DATABASE=PUBS;UID=UserName;PWD=StrongPassword")
   IF gnconnhandle>0
   *!* Create a SQL Server table with Unicode Fields
      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 a FoxPro Database Container
   CREATE DATABASE unitesta
   *!* Create a connection to SQL Server
   CREATE CONNECTION uniconn CONNSTRING "DRIVER={SQL Server};SERVER=My_SQL_70_Server;" + ;
      "DATABASE=PUBS;UID=UserName;PWD=StrongPassword"
   *!* Create a remote view using the SQL Server Table created above
   CREATE SQL VIEW view1 REMOTE CONNECTION uniconn ;
      AS SELECT * 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.
   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
   
   *!* Display information about the datatype properties of the fields in views
   =DATATYPE('View1')
   =DATATYPE('View2')
   
   *!* Attempt to USE View1
   *!* This results in the error message "DataType Property for field 'NChar_fld' is invalid"
   USE view1
   
   *!* Attempt to USE View2
   *!* No Error occurs
   USE view2
   browse timeout(2)
   
   *!* Set the DataType Property for the Unicode Fields, used in View1, to valid settings.
   =DBSETPROP('view1.nchar_fld','field','datatype','c(254)')
   =DBSETPROP('view1.nvarchar_fld','field','datatype','c(254)')
   =DBSETPROP('view1.ntext_fld','field','datatype','c(254)')
   
   =DATATYPE('View1')
   
   *!* Attempt to USE View1
   *!* No Error occurs
   USE view1
   browse timeout(2)
   
   USE
   
   *!* Set the DataType Property for the Unicode Text Field, used in View1, to M.
   =DBSETPROP('view1.ntext_fld','field','datatype','M')
   
   *!* Attempt to USE View1
   *!* This results in the error message
   *!* "Type conversion required by the DataType Property for field 'Ntext_fld' is invalid"
   USE view1
   CLOSE ALL
   ON ERROR
   RETURN
   
   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
   					

2. From the Command window, type:

   DO UNICODE
   					

3. Note the output that displays on screen.
4. 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.
5. Note that the attempt to USE View2 succeeds.