June 5, 2006 at 1:42 pm
Does anyone have experience creating linked server to a FoxPro database/Data file? I know little about FoxPro, but realize that DBC is aFoxPro Database and DBF is a table. My source server doesn't have DBC file, but I was able to locate a handful of DBF files that appear to be the tables that I am looking for.
I am able to create ODBC connection to the DBF file, after downloading the MS VFoxPro ODBC driver. But I have trouble creating Linked Server from SQL 2000 to any of those FxoPro tables. Any help is appreciated.
June 6, 2006 at 8:16 am
Are the tables on the SQL Server or a network share? The account SQL Server is running under must have access to the files, and if your server is running under a local system account it may not have access to the share.
I have had trouble with linked servers for DBF files using the Jet driver for dBase files on Windows 2003 servers. This driver requires some obscure permissions to allow client users to create a separate process on the SQL server to run Jet. It works if I use the sa SQL login but there can be problems with Windows logins, even logins that have sysadmin rights on the SQL server and on the file server. I don't know whether the FoxPro driver suffers from the same problem.
You can try to determine whether it is a security problem by testing the linked server in an sa connection, or by logging into the SQL server and connecting from there.
June 6, 2006 at 8:33 am
As I recall, you have to create a system DSN. Then when you select add new linked server, you specify Microsoft Oledb Provider for Odbc drivers.
You can stick anything you like into the product name field bacause it doesn't do anything.
You put the name of the system DSN in the data source and click OK.
The real issue though is that it is one link per file
This was all I did
Hope this helps
Quis custodiet ipsos custodes.
June 12, 2006 at 4:09 pm
There may not be a dbc (database container) file available.
Tables in Visual FoxPro can exist as a free table or as a database table. A free table is a table (.dbf) file that is not associated with any database. A database table is a table file that is associated with a database. Database tables can have properties that free tables do not have, such as field-level and record-level rules, triggers, and persistent relationships. Database tables also have a dbf extension.
There is an ole db driver for Visual FoxPro available on the msdn web site http://msdn.microsoft.com/vfoxpro/
June 13, 2006 at 6:52 am
Do you know the files were created by FoxPro, or are you just assuming? DBF files could also be dBASE format files created by some other application.
You don't say what the problem is that you're experiencing with your attempts to create a linked server. If it is because these files aren't really FoxPro but some more primitive dBASE format, you might try a dBASE linked server using the Jet provider.
June 16, 2006 at 11:55 am
From the little bit i've been playing with linked servers, you have to use an OLE DB driver (you could use the OLE DB ODBC driver). I've only had luck creating linked servers to dbc's, which we had to create. If you get it working to free table directories, i'd be interested in hearing how you did it.
February 14, 2007 at 9:58 am
This is coming really late, but the following process worked for me when trying to query DBF files using only one linked server and ODBC connection.
Installed Visual FoxPro ODBC drivers on server. (http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx)
(http://download.microsoft.com/download/vfoxodbcdriver/Install/6.1/W9XNT4/EN-US/VFPODBC.msi)
Installed Visual FoxPro ODBC bugfix on server. (http://support.microsoft.com/kb/191685/EN-US/)
(http://download.microsoft.com/download/vfox60/sample10/1/w9x/en-us/vfp6int.exe)
Setup System ODBC connection to point to local directory containing DBF files. I used the "Free Table Directory" option in the setup. Also, I could not get it to work over a share. IMPORTANT: The files had to exist on the server running SQL server.
Setup a linked server on the SQL server using the following properties:
Linked server - [Name of linked server]
Provider - "Microsoft OLE DB Provider for ODBC Drivers"
Product Name - "Microsoft Visual FoxPro Driver"
Data Source - [Name of your System ODBC connection]
After this the following query worked perfectly:
Select * from OPENQUERY('Select * from {filename}')
Even better, the following one worked also!
Select * INTO {TableName} from OPENQUERY('Select * from {filename}')
As you can see, any filename can be used dynamically within the query, allowing for only one Linked Server and ODBC connection.
January 19, 2009 at 7:11 am
Andrew Maxwell - Thanks for the process given here, just implemented it and it works a treat. I was having all sorts of errors using OPENROWSET with MSDASQL but no issues with this so far!
Cheers
April 7, 2014 at 2:02 am
I did the steps you mentioned but there are some problems.
1- Bugfix link is not working. Please provider valid link
2- I have created ODBC driver for with free table directory. When try to create link server it generate an error
Cannot initalize data source object for OLEDB provider. MSDASQL "The Specified DSN contains an architecture mismatch between the driver and application"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply