3

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 ?

Community
  • 1
  • 1
NikT
  • 1,590
  • 2
  • 16
  • 29
  • 1
    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 Jan 31 '17 at 17:44
  • 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 Jan 31 '17 at 18:19
  • 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 Jan 31 '17 at 18:29

1 Answers1

5

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    I confirmed that data on (max) columns returns using ODBC Driver 11 for SQL Server, which is newer than the Native Client and SQL Server drivers I was trying, so it makes most sense to use it, thanks ! – NikT Jan 31 '17 at 18:51
  • 2
    If you're doing anything with Python and SQL Server, I'd highly recommend upgrading to ODBC Driver 13 instead of 11, as 11 had some fairly major issues (especially around threading). For example, to get Django's runserver to run with 11, you needed to use the `--nothreading` runtime parameter. – FlipperPA Jan 31 '17 at 20:36
  • 1
    Just a note that the OLE DB drivers are making a comeback: https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/ – Will Jun 26 '18 at 16:09
  • @Will - Thanks for the tip. I have updated my answer. – Gord Thompson Jun 26 '18 at 17:50