MS SQL: what is the difference ODBC provider and driver , or OLEDB

[mLipok]

What is the difference ODBC provider and driver , or OLEDB ?
and the real question is :
What and why do you prefer, as a way to access the database MS SQL 2005/2008/2012/2014?
 
 
I search with google and found this (but I have not found an appropriate response):
 
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/67ada271-125f-44ad-8ac7-43d95f68b2e9/whats-the-difference-between-database-driver-and-data-provider?forum=sqldataaccess
 
http://stackoverflow.com/questions/19293744/difference-between-driver-and-provider
 
http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources
 
http://www.dotnetspider.com/forum/207266-Difference-between-driver-and-provider.aspx
 
and from here:
http://community.qlik.com/thread/106540
 
I get nice diagram:

[Zedna]

I always use ADO because it's part of Windows OS and it's tuned in AutoIt in very good way.

There is nice reference:

http://www.w3schools.com/asp/ado_intro.asp

[mLipok]

Thanks for your reply. 

As can be seen from the above diagram ODBC, is closer to the data source. 

 

I wonder how it makes sense to use ADO instead of using ODBC ?

 

Whether through ADO get rid of any problems that may arise along the way ?

[water]

ODBC might be a bit faster because you avoid ADO and OLEDB.

ADO is general purpose. You could write a script using ADO to access a text file. If you get more and more records you could easily switch to a SQL database without changing the logic of your script. Just by simply modifying the connection string.

[mLipok]

In a word ADO is more versatile and therefore easier?

but is there any difference in stability with this two cases :

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

[water]

I do not think there is a difference in stability. Choose whichever you like.

[mLipok]

By chance I found the answer to one of my previous questions but also by the way I was able to see the difference.

From here:
http://stackoverflow.com/questions/7164485/application-name-in-sql-server-when-connecting-from-classic-asp

ODBC connections will not pass the application name, but ADO connections will

[water]

I see.

BTW: I have added the diagram you posted in #1 to the ADO wiki entry.

[mLipok]

Very good.

---

from here: http://community.qlik.com/thread/106540#463511

 

* If the user wants to access data independent of the SQL language , you should migrate to OLE DB . As noted , the connectors are linked to ODBC SQL language.

 

* If the user's problem can solve directly manipulating tables , it is best to use OLE DB .

 

* If the user wishes to multidimensional data parallel interfaces , OLE DB is recommended as it is the only standard for data and models with these characteristics, and reduces the required amount of code and execution time.

 

* If the user requires access to different databases (local, shared , IOM, etc. ) from a single application , use OLE DB , otherwise you will be using separate routes for each access method code. This is also based on the idea that the OLE DB connectors reduce the differences between drivers , SQL and DBMS dialects have been problems between the ODBC settings.

 

* If the user need is to run concurrent updates , the OLE DB architecture is the best choice . To update records using an ODBC driver , the user must generate SQL UPDATE statements that are basically oriented batch updates . There is no concept of records in an ODBC block model.

The OLE DB architecture accommodates various models allowing the management of data from many more applications.

 

 

and http://community.qlik.com/thread/106540#463511

 

In terms of performance, any database layer will increase access time at least 40% in comparative with native access.

[jchd]

 

In terms of performance, any database layer will increase access time at least 40% in comparative with native access.

That's roughly what I observed when I last compared ADO vs native (UDF) access on a local SQLite DB. I was wondering if fetching whole 2D recordsets with .GetRows instead of grabbing the babies field after field for every row could offset the burden of the rest of an "average" application (whatever that means), but no.

The main advantage of ADO is that it hides most differences between engines.