ODBC vs OLE DB Connection?

[Former Member]

What are the pros and cons of using an ODBC vs OLE DB connection? I created both of these connections in designer pointing to the same database using MS SQL Server 2005. We are going to be re-pointing all of our Universes to use this new database and are trying to decide which connection to use. We are on XIR2 SP2 and are planning to upgrade to XI 3.1 soon. Are there any known issues or differences with this in XI 3.1?

Any response would be greatly appreciated.

Mike

[Former Member · Accepted solution]

Hi;

my two cents here, but, as you are using a relational DB, I would go for ODBC as it is the oldest and as so most mature technology for this, but also was specifically designed for relational DBs, and thus might contains specific optimizations.

OLE DB is more open and tries to do more things, thus you would eventually lost the specific ODBC optimizations for RDBMs and it's younger than ODBC, and thus maybe more prone to bugs, and might not be available on other platforms (who knows, maybe you'll want to move to another system later).

From what I understand it even looks like that for most DBs (I don't know for MSSQL) OLEDB providers are build on top of the ODBC driver, thus adding one more layer between the application and the data, and thus a little more performances lost...

As you already have both universes, maybe your best option is to do a quick bench by designing and refreshing a few big reports (some reports where the DB is hit a lot).

Regards;

Lionel