Is it 32 or 64?

That’s a question that you should always keep in mind when working on a 64-bit machine.  In case you’re wondering why I’ll tell you a little story.

On a server not so long ago the operating system was Windows Server 2008 64-bit.  The database system was SQL Server 2008, 64-bit as well.

That server needed to be able to connect to a DB2 database on another server.  For that purpose someone installed the IBM ODBC 9.5 driver, 32-bit.  As usual when someone asks for connectivity to that DB2 server, the batch script containing some typical DB2 commands was executed.  What this script does is create a couple of ODBC System DSNs.  The person that installed the driver showed that all was working fine because using some command-line tool he was able to connect to the databases.

However, when I opened the ODBC Data Source Administrator through Administrative Tools > Data Sources (ODBC), it didn’t show me any System DSNs and when I clicked the Add… button it didn’t show me any DB2 drivers either.  So it seemed that something went wrong.  Or not?

What I needed to be able to do was to connect from Reporting Services to the DB2 database.  So as next test I decided to create a small report directly on the server (BIDS was installed).  And all went fine, I was able to create a data source connecting to DB2 and dataset querying data from the DB2 database.

Then I decided to deploy the report and run it through the Report Manager.  Deployment went fine but when I ran the report it could not connect to DB2.  Hmm, is it working now or not?

I was fairly convinced that it had something to do with the fact that the ODBC driver was 32-bit.  The Business Intelligence Development Studio is 32-bit, that’s why it worked fine using the 32-bit ODBC driver.  But the Report Server is 64-bit, that’s why it couldn’t connect to DB2 using the 32-bit driver.

But why wasn’t the ODBC DSN showing up?  After all, it must exist because the BIDS showed me the list of DSNs that were created.

And then I found the answer, as usual, on the internet.  And it was fairly logical but sometimes you just don’t think of it.  The ODBC Administrator that you launch from the Start > Administrative Tools menu on a 64-bit server is the 64-bit version of the tool.  And that 64-bit version only shows DSNs using 64-bit ODBC drivers.  To get the 32-bit version of the ODBC Administrator you need to launch it manually through Explorer.

And now you may be wondering where on earth disk you’re supposed to find that 32-bit version.  Here’s the answer:

• The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
• The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

And indeed, that version of the tool showed me the DSNs and the DB2 driver.

(Why they didn’t rename the 64-bit version to odbcad64.exe – after all, that is what the 32 in the filename means – remains a mystery to me.)

Sidenote: there appears to be an issue with this tool where both the 32 and 64-bit version of the tool show both the 32 and 64-bit DSNs.  My server didn’t have this issue but it may be interesting to keep it in mind.  More details about that issue in this Microsoft Support article.