Accessing 'ntext' fields from SQL Server over ODBC (Windows)

Ben Rubinstein benr_mc at cogapp.com
Wed Jan 12 07:51:02 EST 2011


We've got a problem with reading data from an 'ntext' column in a MS SQL 
Server database.

This has arisen in a client environment where they recently upgraded a 
third-party package.  They use an app we wrote in Rev/LC which retrieves data 
from the backend database of this package; one of the fields we retrieve has 
changed from 'text' to 'ntext', and we're now only retrieving the first 
character in each case.  (We of course have no control over the design of this 
database.)

We've reproduced this locally, setting up a simple test database with one 
table, one ntext column: the table has three rows as follows:
	Row 1 here
	Row 2 here
	ümlaut

Using our app or Rev/LC in the IDE on Mac, this is retrieved as expected. 
Using either the app, Rev 4.0, or LC 4.5 on Windows, we get just
	R
	R
	ü


(and the length of the data returned by revdb_columnbynumber is 1).

On Mac we're using the ActualTech ODBC driver; on Windows the default supplied 
ODBC driver.  In the DSN setup on windows we have unticked all options on the 
final config screen, so there is no character translation or any other options 
on. We have also tried various combinations of these options.

What surprises me is that this works on Mac (using ODBC over the network to 
SQL Server running on Windows), but fails when LC is running on Windows (even 
on the machine that's hosting SQL Server).

(Our test machine was Windows Server 2003, with SQL Server 2008; I'm not 
currently sure the details of the client's network.)

Has anyone else worked successfully (or otherwise) with ntext fields over ODBC 
on Windows?

TIA,

Ben




More information about the Use-livecode mailing list