Legacy Connectivity in a 64 bit world, part 4 of 5

This post continues my five part saga on connecting to legacy data formats. This third data source type (of four types) is Visual FoxPro. I haven't seen very much Fox since 1992, when I was a C programmer and worked with dBase, FoxPro and Clipper for a few months. I thought that everyone left FoxPro and dBase behind in the mid-1990's. Not so.

Visual FoxPro was dead-ended by Microsoft a while back. Microsoft states that the ODBC drivers are depricated and that the 32 bit OLEDB drivers should be used. I can pretty reliably bring down an instance with the ODBC drivers and I am very interested in using something besides these drivers.

There are 32 bit OLEDB drivers freely available from Microsoft but it looks like there won't ever be any 64 bit drivers.

All of the projects which I found on Sourceforge looked too shaky to consider running "for real", and I'm not in a position to start writing or supporting open-source OLEDB drivers. No third-party has stepped up to the plate, and I think that someone is missing a business opportunity here. I know that xBase is dead but given the amount of tools that used to work with FoxPro, dBase and Clipper files, it is odd to me that no one has ported their stuff to 64 bit and produced an OLEDB driver.

The fourth data source type is Excel and Access files. This is even more flabbergasting than the FoxPro situation. For the uninitiated, Excel and Access support is through the "Jet" drivers, which can also do some FoxPro work. The problem for us is that there does not seem to be a 64 bit version of Jet. The replacement for Jet is called "Ace" and is oriented towards the Office 2007 file formats. That doesn't matter because there is no 64 bit build of Ace, either. I'm sure that there will eventually be a 64 bit build of Ace, but we can't wait until the next version of Office ships to get it.

As a workaround, I could potentially import the data to native SQL tables using DTS and/or SSIS using the 32 bit providers if I force the package to run on the server in 32 bit mode. The issue there is that there is heavy use of OPENQUERY at random times during the day to these external data sources. The data sources are not updated on a schedule, so I can't just schedule a package to load the data once every so often. I can't alter the clients, so having them run some sort of code with sp_start_job, xp_cmdshell, the OLE/COM stored procedures or CLR before looking for their data isn't practical. (No to mention that implementing a solution with sp_start_job would complicate our security issues, that we are trying very hard to avoid enabling the xp_cmdshell or COM stored procedures or that running packages in a CLR procedure seems to be very frowned on by Microsoft.)

The solution that we are working towards is to keep a 32 bit instance around for the explicit task of using the 32 bit drivers. In other words, SQLServer/64 talks to SQLServer/32 which does the dirty work and hands the results back to SQLServer/64. This is suggested on various googlable forums around the internet. Our initial testing shows that this is not any worse than directly access files. Most of our effort here will be due to increased administration requirements and security configuration.