SQL Server ODBC drivers - 32-bit or 64-bit

server administration XI Server Discussion
1

We are currently on BI 4.0 SP5, getting ready to upgrade to 4.1 SP5. Our database is SQL Server 2008 R2. When testing the new environment I encountered some db connectivity issues and did a little reading and the light finally went on. I didn’t realize that the client tools are still 32-bit and that’s why we have to use 32-bit ODBC connections on our local machines. But on the server we need to use 64-bit connections. (The driver is SQL Server Native Client 10.0 for both.)

However, we have one universe with a data foundation BEGIN_SQL parameter of SET TRANSACTION ISOLATION LEVEL SNAPSHOT and this was throwing an error on the new server. I switched the connection to the 32-bit one and it was fine. A trace showed that the 64-bit one sends batch commands but the 32-bit one uses RPC calls. Not sure why the batch commands are actually throwing an error, but it seems to need a GO between the SET and query (but there’s no way to do that in BI).

So, a few questions:

  1. Anyone know anything about this difference?
  2. How can the server use the 32-bit connection?
  3. Do all components do this, such as the CMC, or just the universe connections?
  4. When it works, are there any particular issues with using the 32-bit connection?

Basically I’m wondering which one we should use. I’ve seen recommendations to set all connections up in both, but I’m not sure that’s the best answer (and probably won’t work for the particular error I had).

Thanks.

craiggsmith (BOB member since 2009-06-01)

2

Not an expert here, but a couple of observations. For the most part we have Crystal Reports on our BI 4.0 system. Crystal Reports will only work with the 32-bit ODBC drivers. We have a few dashboards (Dashboard design). If we are using UNX as a datasource then we need both the 32 and 64 bit ODBC (named the same) drivers. We do not really do anything with Webi (yet).

kevlray :us: (BOB member since 2010-06-23)

3

You need both (32 and 64 bits drivers) on the server. Webi at least needs 64 bits, client tools (including Rich Client) are still 32 bits.

How did you test that the 32 bit version was better? Rich Client?

If SQL server behaves differently between 32 and 64 bits, this is not really a BO problem, but worth to know.

KFonMurphi :fr: (BOB member since 2007-10-16)