varchar(max) datatype odbc mapping to ms-access2003

Question

Hi there,

i have tested the difference between defining varchar(max) in opposite text with our legacy application on a SQL Server 2005. All went very well and the new datatype reduced in a particular test case the amount of reads from 78 to 8 that represents a performance gain which we dont want to miss.

But now on the target sqlserver of version 2008 i get a problem: the columns of datatype varchar(max) are now mapped to the msaccess2003 datatype text(255).

Where is this odbc mapping defined? Which parameter must be changed to get the desired mapping varchar(max) to memo?

Peace

Ice

Update:

Now i know that when i connect the Access ODBC-Sources while remotely logged on the Win 2008 / SQL Server 2008 the mapping of varchar(max) to ms-access-datatype memo is correct. We work in a LAN and while logged on the Win2003-Citrix-Server connecting the ODBC-Sources with ms-access the mapping goes wrong. Very weird situation, whats is going on?

Answer 1

While this article refers to importing, it may be of interest to you: http://www.windows-tech.info/15/313c8cc3ac03f313.php

It states:

Your field in SQL Server should be an NVARCHAR(MAX) to map directly to the MEMO field in Access. The data types inside the SSIS package for that field should be DT_NTEXT.

-- Phil Brammer