[AccessD] [dba-SQLServer] NVARCHAR turned into Text(255) in Access

One caveat with 8000 character field lengths is that SQL server will
"spread' the record out to 2 pages when it retrieves the record.

In other words, if you want retrieval of SQL data to be efficient you have
to ensure that the total field length falls within 8060 characters
(equivalent to one page of data).

http://www.thescripts.com/forum/thread82625.html

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Wednesday, May 02, 2007 7:11 AM
To: dba-sqlserver at databaseadvisors.com; 'Access Developers discussion and
problem solving'
Subject: Re: [AccessD] [dba-SQLServer] NVARCHAR turned into Text(255) in
Access

It turns out that simply changing it to VarChar(8000) works in this case. I
tried VarChar(64000) and got a message from SQL Server that 8000 was the max
for VarChar.  

This would have to be described as a bug in the converter however, which
could cause data loss (though AFAICT it did not in my case, only because all
of my memo fields are shorter than 8K).  

It also indicates a possible problem with the ODBC driver / connection since
a Memo can be up to 64000 characters and VarChar can only be 8K there is a
very real possibility of data loss in some cases.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Wednesday, May 02, 2007 9:56 AM
To: dba-sqlserver at databaseadvisors.com; 'Access Developers discussion and
problem solving'
Subject: [dba-SQLServer] NVARCHAR turned into Text(255) in Access

I uploaded my billing database to SQL Server the other day.  The process
worked perfectly AFAICT, however SQL Server turned my memo field into a
nvarchar, which when linked back into ACCESS, was now being converted to a
TEXT(255).  Needless to say, this was not my intention, nor is it
acceptable.  Has anyone ever run into this?  Known solutions?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com