Hi,
We are moving from BOBJ on AIX to BOBJ on Win servers. On AIX, it was possible to configure only ODBC drivers. Now with Win servers, also OLE DB is available.
Do you recommend setting up ODBC or OLE DB for MS SQL databases? Can you list pros and cons for both?
Thank you.
Marek Chladny 
(BOB member since 2003-11-27)
I’d tend to use OLE DB where available, if only because it’s native (or possibly more native, they’re both MS) and should give a more comfortable experience.
ODBC is more generic, afaik.
OLE DB is also newer. But only very very slightly.
It’s six of one and half a dozen of another to be honest.
Damocles 
(BOB member since 2006-10-05)
Using OLE removes the need to use ODBC (unless you like that sort of thing).
We ran into a problem with calling some stored procs when using OLE, though it worked without issue with ODBC. We never did find that actual source of the problem, so it’s possible that it may have been a client/server version incompatibility.
joepeters 
(BOB member since 2002-08-29)
The first item is that Microsoft has announced the deprecation of OLE DB. It looks like it may still work going forward but any new functionality that may be added to SQL Server may not be available using it. Something to watch out for.
From a performance perspective, it appears that OLE DB outperforms ODBC at least in some cases. This is particularly evident when using the Integrity Check in the Universe Design Tool. We have several cases where the Integrity Check won’t finish if we are using an ODBC connection but it will with an OLE DB connection.
From a maintenance perspective, there is more maintenance with ODBC connections. You will have a universe connection with both options but with ODBC you have to maintain the ODBC DSN on your servers also. If you are like us and you use Crystal Reports and Web Intelligence, this means you also have to maintain 32-bit ODBC DSNs and 64-bit ODBC DSNs. If you do to the ODBC route, I recommend you get DTM ODBC Manager. This is a freeware tool that provides a graphical and command line interface for the exporting and importing of ODBC DSNs. This can be done for single or multiple ODBC DSNs. We’ve used this tool for several years.
We are looking at utilizing JDBC since the deprecation announcement for OLE DB, but at this point I’m not sure it performs any better than the ODBC which is disappointing.
An upside to ODBC connections is that you can change your data source without having to interact with Business Objects. We have a use case where our users need to temporarily point their universes (and also stored procedure based Crystal Reports) do different data sources due to testing requirements. If you use the same login ID, password, and database name on different servers, all you need to update is the data server in the ODBC DSNs to change the data source for the reports. We use the command line function of DTM ODBC Manager to create a batch file for this. We upload the batch file into Business Objects as a program object and allow the user’s to schedule the program object whenever they need to. This takes us out of the loop of having to do the updates for them.