>I was re-reading the thread (so that I can document my notes; otherwise, I will forget all this discussion by this coming Monday :)
>You said that CA does not work with ODBC but with OleDb. I am not arguing; I am sure you know what you are talking about. But my question is, how does changing the driver from "SQL Server" to "SQL Server Native Client 11.0" changes the OleDb? I thought that when I specify the connection via the "SQL Server" or "SQL Server Native Client 11.0", I am specifying the ODBC driver. But if I understand you correctly, the driver I specify affects the OleDB as well. Could you, please, if you have a free moment explain?

ODBC and OleDb drivers are completely different animals and FoxPro interfaces with them completely differently (ie. Remove Views and SQLPassthrough use ODBC, DataAdapters use OleDb). OleDb is a COM based protocol, while ODBC is a raw API based protocol that Windows provides (on Windows anyway - on other platforms the drivers come from other sources). The OleDb driver support on FoxPro tends to have much better support for newer features, because a) it's much newer when it was introduced, and because types are translated through the Windows COM system rather than through FoxPro. The COM -> FoxPro translation can map better to FoxPro data than the full ODBC raw SQL data types.

The ODBC interface in FoxPro can't deal with some of the type conversions that the ODBC drivers provide (not sure why - especially for strings) but I think it has to do with how the schema information is passed and processed by FoxPro. There was no VarChar(Max) when FoxPro was discontinued (I think maybe came out right around the same time) so FoxPro just doesn't know what to do with that. As I mentioned varchar(max) doesn't work because FoxPro's use of the ODBC driver can't figure out the width for the 'max' value and wasn't set up to map to memo automatically but it works fine with using a very large value as that value is turned into a memo. Not sure what that does to overhead/memory usage inside of the OleDb driver, but I suspect it's not a big deal as I've returned massive amounts of memo data with varchar(999999999) and didn't see a mem problem that would account for a 1/2 million records with 999999999 bytes per field :-)

+++ Rick ---

>
>TIA
>
>
>>Unfortunately this is a deep morass of ODBC hell that is very difficult (if not impossible to parse reliably) to fix with a single driver. CursorAdpater works because it doesn't use ODBC - it uses the OleDb driver, which has updated integration in VFP that supports translating newer types.
>>
>>re: ODBC, there's is a ton of discussion around different drivers and what they do and don't support on this West Wind Message Board Thread:
>>
>>Create Sql Server Tables Error
>>
>>My takeaway from that discussion was this: The safest choice for most things is to use the generic FoxPro ODBC driver, rather than the native drivers. That works with most things including Varchar(max). If you use newer ODBC features like JSON or XML fields etc. then use the SQL Native driver and **selectively** switch to that driver for those queries that require it. If necessary you can cast query selectors in the SQL to types that FoxPro can deal with (like casting varchar(max) to text for example or a fixed width).
>>
>>Some things are also helped by using unicode fields rather than ANSI fields in the database (ie. using nvarchar instead of varchar) as switching to Unicode and the unicode translations in the VFP ODBC interface performs some additional translation.
>>
>>+++ Rick ---
>>
>>
>>>Hi,
>>>
>>>I have been testing my VFP 9 application with the SQL Server Native Client 11.0. I changed the type Text with Varchar(MAX). I use CursorAdapter.
>>>
>>>Here is what I come across.
>>>1. Good thing is that where the data is retrieved and saved via CursorAdapter, I have no problems.
>>>
>>>2. Then I tried to get the data from the column of type Varchar(MAX) without CursorAdapter. I do SQL Select and then assign the value of the column to a variable. The variable is EMPTY.
>>>
>>>So, it appears - please correct me if I am wrong - that with the SQL Server Native Client 11.0 and type Varchar(MAX), I must always get the data via CursorAdapter. Correct?
>>>
>>>TIA
>>>
>>>UPDATE. Since most if not all of my reports get the data from the SQL Server by SQL Select, I need to find a way to MAP the VarChar(Max) to a MEMO field, without CursorAdapter. Is it possible? And if yes, how?