64-Bit Windows and ODBC Drivers

A slightly more technical article for today but something worth mentioning.

At work, I am attempting to get a direct connection between MS SQL Server 2008 and an Oracle database. Some might say that such connectivity would bring about the end of the world or a hole might open in the ground and swallow me up. Anyway, this is the software configuration at the moment:

• the server I am using runs Windows Server 2008 R2 (64-bit),
• the database software running on the server is MS SQL Server 2008 (also 64-bit),
• the Visual Studio development tools for building SSIS packages are 32-bit),
• the Oracle Client software is also installed (32-bit and 64-bit).

The basic principle when connecting to databases from a given machine is that 32-bit software will require a 32-bit ODBC (Open Database Connectivity) driver or client and 64-bit software will require a 64-bit ODBC driver or client. This is quite similar to how you need 32-bit drivers for a 32-bit operating system and 64-bit drivers for a 64-bit operating system. If these things don’t line up then you won’t get the right results.

The other thing to keep in mind that on 64-bit Windows you can create both 32-bit and 64-bit ODBCs but, if you use the “Data Sources (ODBC)” applet in “Administrative Tools” you’ll only get to configure 64-bit ODBCs. Therefore, it’s important to know how to get to the 32-bit “Data Sources (ODBC)” applet and the 64-bit version separately:

• C:\Windows\SysWOW64\ODBCAD32.EXE (32-bit)
• C:\Windows\System32\ODBCAD32.EXE (64-bit)

Yes, it might be contrary to what you might initially thing but the executable in System32 is the 64-bit version while the SysWOW64 copy is the 32-bit version. There’s a technical reason why this is the case but I won’t go into that here.

It’s important to have uniformly named ODBC entries for both 32-bit and 64-bit just in case you are using a hybrid deployment process such as my situation where the database server itself is 32-bit while the development software is 32-bit.