Thank you for your suggestion.

>Before embarking to manually change a couple of hundred SQLs, testing if a function of about a dozen lines might enable a global replace from SqlExec(tnHandle, tcSQL, tcAlias) to SqlExec_overCA(tnHandle, tcSQL, tcAlias) while leaving all code intact probably is not "TONs of work"
>Your time IAC ;-)
>
>regards
>thomas
>
>>My typical report has a VERY long SQL Select string. This string has several Where clauses, several Join classes, sometimes Group By clauses, and on and on. Then this string is processed by SQLExec and a query/cursor returns. Kind of a simple description.
>>Inserting into the SQL Select a couple of "cast(field1 as Text) as Field1 (since most tables have one or two VARCHAR(MAX) fields) is easy, just time consuming.
>>Your approach of changing everything to CA would work, probably. But I doubt it would take less time, run faster, and provide a better maintenance.
>>Thank you.
>>
>>>Coming in late I realize Ricks solution probably is closest to the "MAP" facet of update part in your own OP.
>>>
>>>As you did not go into specifics on how many different servers/connections you use, how many different ways of creating data environment for your reports are in place to create the "TONs of work" for you, my tip might make short work of a few TONs or be totally out of place ;-)
>>>
>>>My first gut reaction was to create a generic function to wrap those report SQLs,

>>>passing the query as parameter
>>>creating a local CA or connecting to global report CA
>>>feed SQL function parameter into .SelectCmd
>>>nullify .CursorSchema to force automatic generation
>>>call .Cursorfill()
>>>if necessary, detach cursor from CA
>>>

>>>Could be less work depending on you existing code, plus having standardized on backend access in your app is secondary benefit, having single place every SQL runs through another benefit if you decide to measure/log query times without much effort.
>>>
>>>my 0.22€
>>>thomas
>>>
>>>>I tried the cast, as you suggested. And it works with the varchar(max) and the Native Client. Here is how I did it:
>>>>

>>>>select (cast myvarchar as text) as myvarchar from my table
>>>>

>>>>And it works! This is TONs of work for me to go through all reports and instead of getting the varchar(max) directly, change it to CAST as Text. But at least there is a solution.
>>>>
>>>>Unless I miss something else :)
>>>>
>>>>Thank you so much!
>>>>
>>>>>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?