>SQL Server Native Client doesn't work with varchar(max) but does work with Text (but they tell you not use text). The upside of using SQL Server Native Client is that it works with the new Date field types in SQL Server 2008.
>
>If you use the other drive (can't remember the name right now), then varchar(max) works correctly the the date field comes over as a string.
>
>For what it's worth I use the SQL Server Native Client with SQL Server 2008 and use Text fields with no negative effects (millions of records) and using full-text indexing.

The limitation I ran into with Text fields is that they can't be part of a GROUP BY clause. No big deal, except this also means no SELECT DISTINCT... which also effect UNIONs (unless you use UNION ALL). Switching to varchar(MAX) solved those problems for me, and I got around the Char(0) problem using a CursorAdapter with a schema. I don't think SQL Passthrough allows you to specify a schema.