Sql Server x64 and x86 Linked Server

Question

I have a Visual FoxPro table that I need to access from Sql Server. In Sql Server x86, I would just create a linked server. Unfortunately, there is no x64 driver for VFP - so Sql Server x64 can't create a linked server to it.

So far, I've come up with following options - none of which I'm particularly fond of:

1. Set up an x86 Sql Server to be used as a relay, so that queries go from x64 -> x86 -> VFP.

I don't really care for this, as in addition to being dev, I'm also sysadmin. So, this means I need to patch, maintain, and monitor yet another Sql Server - and possibly yet another server (assuming I don't just use a separate instance).

Also, since the VFP provider doesn't work with 4 part syntax, I have to use OPENQUERY. Thinking of all the single quote escaping that'd need to happen to have an OPENQUERY statement embedded into another OPENQUERY statement makes my head spin....

1. Create a CLR Table Valued Function, though the assembly would (presumably?) also be x64 - so I'd have to go out of proc (IPC? Webservice?) to actually run queries

Turns out that TVFs require a schema, so this option isn't as clean as I initially thought. I did a spike to get a WCF client into MSSQL, which returns a single column of XML that can then be parsed with the Sql XML datatype functions. It works, and is actually a little bit nicer to query than OPENQUERY since it actually takes variables as parameters. That saves me most of the single quote and EXEC dance.

Of course, WCF inside Sql is wholly unsupported, and smells like a pretty big hack. I have pretty serious reservations on performance and reliability.

1. Stop making queries from Sql Server to VFP, and rewrite a good bit of client code

Obviously, this is the "right" answer. But, there is a good deal of client code that relies on joins between Sql Server tables and VFP tables. Rewriting this stuff to populate a temp table or do client side joins seems like a rather large burden.

Here's hoping someone can suggest a better alternative, or some similar experiences.

Answer 1

It's a nasty problem, I agree.

SSIS run in 32-bit mode to import the data on a regular basis (perhaps on demand, in a job triggered by the same SP) to a SQL Server native table is another option if you can stand the delay. It would depend on the frequency of data change and problems with chance of slightly out of date data.

Answer 2

I think I found an alternative. Microsoft has released an updated driver for Access, which comes in both 32bit and 64bit flavors. Like the original Jet OleDB driver, this will allow you to access dBase file formats from SQL Server x64.

The only restriction is that the DBF must be in one of the dBASE formats supported by ISAM. I have done a few tests using a dBASE IV format and it seems to work, using the following connection string.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;