Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why so many connection providers for database ? Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 10:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
I see several connection types for databases - OLEDB, ADO, ADO.NET etc. AFAIK, ADO.NET has a convenient and easy to use API in scripts. Also, if you want to retainSameConnection inside a .NET script, its easier to use a ADO.NET result set rather than OLEDB as mentioned here - http://stackoverflow.com/questions/11867639/connect-to-sql-database-inside-script-task-in-ssis

Then why do we have so many types of DB connection managers ? Why not choose the most superior one and have only that ? One reason could be that .NET might not be installed on target machines. If yes, then are there any valid reasons for not installing .NET on target machines ?
Post #1533759
Posted Wednesday, January 22, 2014 1:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 13,557, Visits: 10,434
It depends on how you want to use it I guess. It's about having a choice.
Each type of adaptor has its own strenghts and weaknesses. I've done tests where OLE DB was faster than ADO.NET.
Should I throw away performance because someone else wants to use the connection manager in an API? No, I want to have a choice.

Some components only work with OLE DB (lookup), some work only with ADO.NET (CDC). Some providers are only available in ODBC.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533827
Posted Thursday, January 23, 2014 8:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
If I am MS, I would also like to appeal to as many customers as possible

The heart and soul of an ETL tool is the ability to connect to as many sources as possible and load a large amount of data to one target.
Post #1534142
Posted Thursday, January 23, 2014 8:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 5:59 PM
Points: 9, Visits: 57
The connection protocol is often decided by those that wrote the client software
and what they decided to develop their software in:

Roughly, but not exclusive or correct in all cases.

OLEDB - Probably a C or a C++ program
ADO - Visual Basic, MS Access
ADO.NET - Microsoft .NET programming language
ODBC - Some non-Microsoft development package

As was said. MS is just catering for the largest audience possible.
Post #1534322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse