What are the differences between OLEDB/ODBC drivers when connecting to SQL Server?

Question

I have an SQL Server database, and I need to push data into it through vbscript, as well as pull data into Excel. I have found multiple connection strings, but no repository for the benefits of performance and functionality comparing them. The driver options (Provider=) I have found so far are:

• {SQL Server} (ODBC)
• SQLOLEDB (newer than ODBC, but being deprecated?)
• SQLOLEDB.1 (what Excel 2016 uses when clicking 'Get External Data', but not even mentioned on connectionstrings.com... I assume a newer version of the above, but still the deprecated technology?)
• SQLNCLI11 (native client, OLE DB)
• {SQL Server Native Client 11.0} (native client, ODBC)

Different things I read say that ODBC is better because it has been around longer. And that OLE DB has been around long enough to have the same advantages. And OLE DB was made to work with a certain company's applications. And ODBC was made by the same company. And OLE DB can connect to and from different kinds of applications better. And ODBC works better with databases. And Native is...Native, so must be better... because of the name?

I find multiple questions here on SO floating around with no or partial answers, or having multiple comments claiming the answers are out of date. So, as of now, what the specific differences between these different drivers? Do they have different performance in different circumstances? Do they have different features? Do I need to do profiling to determine the best performance and reliability for my particular use case, or is there a standard "best practice" recommended by Microsoft or some recognized expert? Or are they all basically doing the same thing and as long as it's installed on the target system it doesn't really matter?

Necroscopy

We use SQL Server as back-end with Access and Excel on front-end with native client. All works fine. – Solangesolano

Have a look at this: #103667 – Jovial

@Jovial Thank you. That is definitely the first question to come up in search, and of course required before anyone would post a question like this. This question specifically is also what I was referring to when I said "multiple comments claiming the answers are out of date", but isn't the only one. I assume that is why you pointed it out, so that others who haven't done the searching you have done can come into the conversation easier, right? – Necroscopy

@CWilson, You have posted a very good question. I also could not find a comprehensive summary article that explains all these things as they stand in 2016. The information is out there but it is spread out over many different articles, so makes it hard to build a picture. – Jovial

As "correctness" can be opinion-based, perhaps this should be edited to focus more on what the pros/cons/differences are of the various drivers? – Upchurch

@PeterCooperJr. I am not opposed to a title change. I am bad at titles, still trying to learn. I thought both single quotes in a title and a controversial word like correct might get clicks...? I hope that I wasn't out of line, but I obviously wasn't correct. :) However, pros/cons/differences questions are sometimes tagged as too broad? Frankly, the tags should probably change too... I just wanted to fill in more tags, hoping that might attract attention. Also wrong. The input/output software I am using is tangential at best to the core of this question. Suggestions, anyone? – Necroscopy

@Yvette, I have to disagree see the link I have provided above to a similar question. The questions is asking to summarise currently supported drivers/APIs for SQL Server and their relative merits /use cases. – Jovial

@CWilson, alas, the answer is not to be. If you are keen on getting the answer maybe you can try editing the question a bit. – Jovial

I've tried making the wording a bit more concrete and asking about the specifics of what's different between the different drivers; feel free to edit further if I'm not conveying what you're intending. I do think that this is a good question if we can narrow it to listing specifics of the differences. – Upchurch

@PeterCooperJr. Yes, you understand well what I am intending. I need to make sure that the basic framework of the system I am building will last at least 5 years. I saw my two options in solving the driver technology problem to be either 1)to learn what the options and differences are, or 2)violate client confidence in detailing exactly what I am trying to accomplish and say "give me teh codez". I will need to be considerably more tired and frustrated before I start considering the latter, and I don't seem to have the experience to be able to find the research starting point for the former. – Necroscopy

@Jovial Yes. <sad face> I think if I knew just a little more about the technology, I could fix this, but I just am too inexperienced to know where to start. Is there any way to 'community-ify' a question after asked, in a way to invite a DBA or database developer to edit the question as necessary? As asked, this is really the fringe of this PM's capabilities. On the budget I have, I suppose a coin flip to decide the driver might be in order, but I don't want to be 'that guy' who doesn't care about his work. – Necroscopy

This should be reopened as it's a very valid development question. It's about shedding some light on Microsoft data access technologies. – Aeniah

SQL Server 2012 is the last version to support SQL Server Native Client OLE DB provider. ODBC becomes the future provider to use. Anyway, there are many differences between OLE DB and ODBC. See the official Microsoft technical article - msdn.microsoft.com/en-us/library/hh967418.aspx – Arborization

There is a good write-up on the differences between ODBC and OLE DB here sqlmag.com/sql-server/ole-db-or-odbc – Communist

@Mangist Looks like a great article, specifically discussing SQL Server. Thank you. Is 1999 the most recent word on the subject? Is everything written in this 1999 article still completely up to date and accurate? Is this article, and other answers/comments on StackOverflow and elsewhere still up to date and the comments suggesting otherwise inaccurate? – Necroscopy

While I'm unbelievably reluctant to say this, "the question is overly broad". the difference is that "they're different". Nearly everything is different except that they both talk to the database. The object model is different, the query syntax is different, the data types are different. It might be easier to list the things that are the same. That said, people generally choose ODBC when there's some sort of preexisting requirement for it. – Gutter

Is this question relevant? #26357172 – Necroscopy

@PeterCooperJr. Not sure about editing those two additional questions into to the question. Not ready to roll it back, you may be right, but I think that best practice not really relevant here. I think that knowing the actual differences/purposes/limitations/etc. are more useful to making a decision than asking if MS has said to not use one or more of the various drivers. Then again, if MS has in fact said not to use some of these drivers (I don't get the impression that 'deprecated' means that, in this case), that would be useful, but I don't think that exists. – Necroscopy

@CWilson: Sorry; I'm not trying to diverge from your goals. It's more that I thought that it would be less opinion-based and more concrete and answerable to ask for any relevant documentation from an authority on the subject. Feel free to refine further, though at this point it's starting to look like the answer is really that nobody knows for sure, and people's applications just continue to hum along with whatever they happened to use at the time… – Upchurch

You might want to have a look at: https://mcmap.net/q/604697/-oledb-v-s-odbc-duplicate – Brookite

Un-Deprecated? blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/… – Necroscopy

Answer 1

ODBC-it is designed for connecting to relational databases. However, OLE DB can access relational databases as well as nonrelational databases.

There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn't do this with ODBC, because it's not a relational database. However, you could use an OLE DB provider to accomplish that.

http://www.sqlservercentral.com/Forums/Topic537592-338-1.aspx

Mackay