\nLet's talk about clients and SQL Server 2008. First, a little history…\n<\/p>\n
\nEvery new version of SQL Server seems to include a new feature that requires a change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by both SQL Server and Sybase. Since version 4.21, SQL Server's version of TDS and Sybase's version have "grown apart". Because TDS includes version negotiation you can still use old libraries to talk to newer versions, albeit at reduced functionality.\n<\/p>\n
\nEven if they don't update the TDS protocol, there will always be new features that require changes to the client stack at some level.\n<\/p>\n
\nSQL Server comes with support for the following client stacks:
\nODBC – Open Database Connectivity, related to the ANSI SQL standard, vendor neutral
\nOLE DB – a COM-based vendor neutral library
\nADO.NET – a .NET-based vendor neutral library\n<\/p>\n
\nADO (classic) is an IDispatch-friendly library over OLE DB
\nLINQ to SQL and Entity Data Model use ADO.NET to talk to the database. Remember LINQ to SQL is actually LINQ to SQL Server at present.\n<\/p>\n
\nIn addition, Microsoft ships a JDBC (which does not stand for Java Database Connectivity, the docs say so \ud83d\ude09 driver separately. DBLIB also still works, but not shipped with SQL Server any more. It's functionality is limited to features that existed in SQL Server 6.5, IIRC. Other vendors (e.g. DataDirect) ship SQL Server clients too. DataDirect licenses the TDS stack.\n<\/p>\n
\nBefore SQL Server 2005, providers and drivers were part of MDAC. MDAC (Microsoft Data Access Components) was once a separate install, but now its part of the OS. TDS libraries were separate DLLs (e.g. DBMSSOCN.dll). This stopped in\n<\/p>\n
\nSQL Server 2005, and there are two main distribution vehicles:
\nSQL Native Client (SQLNCLI): OLE DB, ODBC, and network libs
\nADO.NET System.Data.dll: SqlClient provider and network libs\n<\/p>\n
\nSo how does this happen in SQL Server 2008? For OLE DB and ODBC, there is a new version of SQL Native Client, version 10. Since MDAC is now part of the operating system, if you install SQL Server 2008 over SQL Server 2005, you'll now see three ODBC drivers, on my CTP4 + VS 2008 beta 2 system:\n<\/p>\n
\nSQL Server version 2000.86.1830.00
\nSQL Native Client version 2005.90.3042.00
\nSQL Server Native Client 10.0 version 2007.100.1049.14\n<\/p>\n
\nFor OLE DB there are three providers:
\nMicrosoft OLE DB Provider for SQL Server
\nSQL Native Client
\nSQL Server Native Client 10.0\n<\/p>\n
\nFor ADO.NET, the situation is a bit more interesting. There's a revision to System.Data.dll to include the new functionality. The new version currently ships with Visual Studio 2008 Beta 2 and its version number currently is 2.0.50727.1378. It simply replaces the version that's installed on the system (and in the GAC) at the time. There's only one ".NET 2.0" version registered in the GAC, as "Version Number 2.0.0.0". Hmmm….seems very "MDAC-like with MDAC as part of the operating system" (that is, the operating system includes .NET 2.0).\n<\/p>\n
\nIf you want to use the new SQL Server 2008 functionality, like date, time, datetime2, dateoffset, table-valued parameters and large UDTs\/UDAggs (and perhaps FILESTREAM support when it arrives) with OLE DB or ODBC, you need to use the new driver\/provider. This means changing the connection string. AND RETESTING. This also applies to SSIS packages, Reporting Services reports and anywhere else you used OLE DB or ODBC.\n<\/p>\n
\nIf you only install the operating system and SQL Server 2008, you won't have the original (version 9.0) provider and driver. These will be provided as a separate download, for folks that have installed only 2008 but haven't restested.\n<\/p>\n
\nBTW, once upon a time, there was talk of "multiple versions of SQL Native Client running side-by-side, using the fusion SxS loader". That didn't happen. The new providers are separate and are registered in the registry separately. No SxS magic needed.<\/p>\n","protected":false},"excerpt":{"rendered":"
Let's talk about clients and SQL Server 2008. First, a little history… Every new version of SQL Server seems to include a new feature that requires a change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,29],"tags":[],"class_list":["post-765","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sql-server-2008"],"yoast_head":"\n