What are the differences in using driver {SQL SERVER} vs Native Client

3
votes

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.

https://connect.microsoft.com/SQLServer/feedback/details/419996/sql-native-client-returning-blank-fields-to-excel

https://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client

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 ?

1
sql-servervbaodbcpyodbc
Have you tried using DRIVER={ODBC Driver 11 for SQL Server}? You seem to be saying that it is installed, and it would be the newer than SQL Server Native Client 11.0 (SQLNCLI11.DLL, circa 2012). - Gord Thompson
Also, verify that pyodbc.version returns '4.0.3'. There have been some fairly recent changes to pyodbc that might address the issue. - Gord Thompson
Thanks, I tried DRIVER={ODBC Driver 11 for SQL Server} and it also works; and I suppose that being newer makes sense that it would take into account newest features so I shouldn't exactly have to worry about what the differences are. Still curious but I should probably let it go haha - NikT

1 Answers

3
votes

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 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").