This question is more about the "why". When you specify DRIVER={SQL Server Native Client 11.0} in a connection string, varchar(max) columns are returned blank. This issue seems to have carried on over the years, and some workarounds exist.
While it seems crazy that these issues still exist, a valid workaround, suggested by:
https://stackoverflow.com/a/33883901/4258124
is to change the connection string to use:
DRIVER={SQL Server}...(rest of connection string)
instead of:
DRIVER={SQL Server Native Client 11.0} ...(rest of connection string)
I've tried this, and it works, but my question is more about "why". If DRIVER={SQL SERVER} is an older driver, how is it able to connect when I pass newer features like MultiSubnetFailover=Yes/True ?
I noticed I have installed (windows, from odbcad32.exe > Drivers) "ODBC Driver 11 for SQL Server" (MSODBCSQL11.DLL dated 2014) and "SQL Server" (SQLSRV32.DLL dated 2010).
In VBA or Python on Windows, does passing {SQL Server} pick the newer of the above two ? Would any features be lost in using {SQL Server} over {SQL Server Native Client 11.0}, and if so, what would they be ? Is there a better workaround ?
In VBA or Python on Windows, does passing {SQL Server} pick the newer of the above two ?
No. {SQL Server} is simply the name of the (very old) SQL Server driver that ships with Windows. It is still used in a lot of places, but it does not support the newer features of SQL Server and has probably been deprecated for a long time now.
In fact, the family of {SQL Server Native Client ...} drivers has had also been deprecated in favour of the {ODBC Driver __ for SQL Server} drivers (where __ is currently either 18, 17, 13, or 11). However, as Microsoft is wont to do, it has changed its mind and "undeprecated" the OLE DB access method (previously "SQLNCLI11", now called "MSOLEDBSQL"). .