I have a native C++ application that runs under Windows 64, it doesn't use ATL (and will not). I want to be able to connect from my application to SQL Server 2012 over the network.
I have the following requirements:
Performance is the key
The C++ will only insert records (say by calling Stored Procs), nothing else (no selects, deletes or updates).
Record insertions should be non-blocking (async).
I narrowed my choices down to OLE DB and ODBC as they satisfy the criteria above. However, it seems that the Internet have loads of contradicting advice of what to use. Examples:
"If you have a choice between using ODBC or OLE DB to access your SQL Server database, choose OLE DB, as it is generally faster. [7.0, 2000, 2005] Updated 2-20-2006" source: http://www.sql-server-performance.com/2007/odbc-oledb/
"OLE DB is deprecated" source: http://weblogs.sqlteam.com/dang/archive/2011/09/04/rip-ole-db.aspx
"Avoid using OLE DB for new SQL Server application development. Update your technology roadmap to move towards migrating existing SQL Server applications that use the SQLNCLI, SQLNCLI10, SQLNCLI11 or SQLOLEDB OLE DB providers to the SQL Server Native Client ODBC driver." source: http://weblogs.sqlteam.com/dang/archive/2011/09/04/rip-ole-db.aspx but SQLNCLI11 is the SQL Server Native Client ODBC driver based on MSDN: http://msdn.microsoft.com/library/ms130904.aspx so, was it a typo in the blog?
I am really confused and would like to take more opinions on the best technology to access SQL Server with my criteria.
Both OleDB and ODBC use the SQLNCLI. See the Component Names and Properties by Version table, observer how Sqlncli.dll/Sqlncli10.dll/Sqlncli11.dll is the driver for both ODBC and OleDB.
If you look at Data Access Technologies Road Map you'll see that both ODBC and OleDB are current and supported. What is deprecated is SQLOLEDB and SQLODBC, which are the old MDAC SQL Server drives for OleDB and ODBC (ie. sqlsrv32.dll and Sqloledb.dll). For both of them there are upgrade paths and recomendations, see Updating an Application to SQL Server Native Client from MDAC.
So the conclusion is that you can safely continue to use both ODBC and OleDB, just make sure you use the modern SLNCLI based drivers and not the old, deprecated, MDAC drivers.
The main risk in using deprecated drivers is lack of support for new data types (eg. geography, hierachy, datetime2 etc etc).