When I first joined the industry in the late 90’s, Microsoft was in the process of shunting the ODBC driver into the background in favour of OLE-DB and ADO. The ODBC driver was still included in Microsoft’ Windows MDAC framework, but it had gained a rather unfair reputation for obtuse connections strings, unreliability and slow data access. They had to include ODBC, as it was and remains, the only way to connect to the more exotic data sources.

Most VB/ASP developers adopted ADO and OLEDB, as they were promised enhanced performance. The connection strings were hardly any less obtuse but developers persevered. Lucky .NET programmers are now provided with ADO.NET, which is the central database access machinery and allows you to connect with SQL Server via OLEDB, SQLClient, or ODBC.

However, things are more confusing for those stuck with unmanaged applications. When SQL Server 2005 appeared, Microsoft chose not to update MDAC to support the new features and data types. Instead, they introduced SQL Native client (SQLNCLI), offering combined ODBC and OLEDB functionality in a single DLL and included support for all the new data types – UDTs, XML, varchar(max) – as well as snapshot isolation, Multiple Active Result Sets, and so on. Any stuffy old unmanaged ADO /MDAC apps that didn’t need access to these exciting new features could continue to use MDAC, which Microsoft hurriedly renamed to ‘Windows DAC’; otherwise the apps had to be migrated to the SQL Server Native Client.

It does seem that the native client is more powerful and reliable than the MDAC ones, which had a tendency to “break” an application every time a new Windows service pack was applied. Unfortunately, those brave souls who chose to move from MDAC to the native client, in order to exploit the new features of SQL Server entered a minefield. While MDAC was tolerant of less than strict adherence to the specs (such as starting parameter names with “@”), the native client isn’t, and will issue errors. There are also all sorts of subtle variations in behaviour to deal with, in regard to connection strings, dealing with failed connections, warning and error handling, and even in basic transaction handling. And it isn’t as if the support for new features is complete. Yes, the native client supports the xml data types, and FOR XML queries…but that’s it. No other part of XML is supported by the native client. You have to use SQLXML for this.

How did we find ourselves in this mess on an issue as fundamental as database connectivity? Ten years ago Microsoft lost enthusiasm for the ODBC standard, and it is left mainly to “sticks in the mud” like Phil Factor to point out that what we dismissed was actually worth having: a fast, truly open standard for accessing all data sources. If it had been properly nurtured and developed by Microsoft so that it supported the full standard, it would doubtless have been loved by all.

Instead new drivers come and go, along with new standards for data access, such as LINQ and EF, so that people are left paralyzed by choice, and unable even to safely upgrade their applications to support the newest version of the database.

Cheers,

Tony.