Thursday, 20 August 2009

32bit ODBC/OLEDB on 64bit Server

This is something that I see questions about all the time. The quick answer is that QlikView 64bit cannot use a driver that is compiled for 32bits.

Why?

If I run an application on a 64bit server in 32bit mode, it has access to all the 32bit OLEDB/ODBC drivers. For example, I have a VBScript that generates OLEDB connection strings. If I run this using the %sysdir%\SysWOW64\csript.exe (the 32bit version of vbscript), I get the following list of OLEDB drivers:



I can see that the Microsoft Jet driver (for Access) is available.

However, when I run my VBScript application in 64bit mode, I get a different list:



No Access driver anymore!

The Microsoft Access drivers are old and have never been compiled for 64bits. This means that a 64bit application running on a Windows 64bit server cannot see them or use them.

QlikView 64bit is not a 32bit application that just runs on a 64bit server, it is an application that is fully compiled to operate in 64bits. I doesn't even know that the Access driver exists because Windows doesn't tell it about it.

So, the limitation is not to do with QlikView, it is a limitation of the company who have delivered the driver.

Luckily, most large database companies (Oracle 10g+, Teradata, Microsoft, etc.) have either OLEDB or ODBC drivers that work in 64bits.

If you just can't get your hands on a 32bit driver? Well, you could go down the route of installing 32bit QlikView client on your server (or another server?) and use that in Command Line mode to access the 32bit driver, read the data and store into QVDs that your 64bit publisher can read. This will of course consume one of your CALs or a developer license, so it is not idea.

The ideal solution is to see what you can do to get a 64bit version of the driver.

3 comments:

  1. I specialize in data access, and this is a continual source of confusion made worse by the fact that User DSNs created using 32bit drivers can show up in the 64bit ODBC Data Sources Administrator and vice versa. That aside, there are solutions for people who have 64bit applications and 32bit data access drivers. Choose a multi-tier type driver with a 64bit client compatible with the application and corresponding 32bit server components that are compatible with the target DBMS or can bridge to 32bit DSNs created using the 32bit driver. As long as the driver's client and server components speak TCP to each other, you shouldn't have problems.

    Best regards,
    Netrista Khatam
    Technical Services Manager
    OpenLink Product Support

    ReplyDelete
    Replies
    1. Hello Netrista,

      I don't know if you'll see this, but I'm looking for a multi-tier driver I can use to connect from 64-bit Qlik to 32-bit Access. Can you make a recommendation?

      Delete
  2. Thanks for the info Netrista. Really good.

    ReplyDelete