Legacy Connectivity in a 64 bit world, Part 1 of 5

One of my projects involves moving some very old applications running against a 32 bit SQL Server 2000 instance to a 64 bit SQL Server 2005 instance. When I the project was initiated, I thought my largest problems would be:

• Differences in behavior between 2000 and 2005
• Porting a couple of hundred DTS packages to SSIS

Wrong. It turns out that my biggest problem is a lack of 64 bit OLEDB drivers.

I have had reason to spend a good deal of time researching the availability of 64 bit solutions for certain legacy data formats. This is a summary of that research and I hope that it saves someone some pain. Anyone could do what I have done here by spending time searching with google. I don't know anything unique, nor do I have any insider knowledge. Still, I was surprised by the situation on the ground.

An early version of this originally appeared on Swynk email lists, specifically the sql2k5 list. It's been edited to tighten up some of my informal writing style. I have also added some new details and information.

(FWIW, I've been reading and occasionally sometimes posting to the Swynk lists since some time during the Clinton administration. I gave up reading usenet postings years ago and I find "web" forums too clunky to use on a regular basis). I think that the only thing that I've been reading longer than swynk is slashdot.)


To begin: In a 64 bit SQL Server environment, you must have 64 bit drivers for OPENQUERY to use. In other words, a 64 bit SQL Server won't run 32 bit OLEDB drivers.

One can get around this problem if the codebase is in DTS. In that scenario, it is possible to force the execution of DTS packages in a 32 bit environment on a 64 bit Windows OS. Another possibility would be to run the packages on a dedicated 32 bit SQL Server. This has performance and security considerations. (Generally, I like to run packages on the destination server.)

Unfortunately, our code base has a lot of OPENQUERY usage. OPENQUERY is used in stored procedures that are executed by user actions throughout a day, as well as by scheduled jobs. Theoretically, all of the OPENQUERY could be rewritten as DTS or SSIS packages and then run by xp_commandshell or via on-demand jobs. I'm leery of the performance and security implications of that, plus that would be an overwhelming amount of work.

So, what to do? Details in the following posts...