Linked Server to connect using Advantage OLEDB from MS SQL

I have created a linked server on SQL Server 2008 instance using Provider Advantage OLE DB to connect to a database consisting on .DBF files. I right-clicked and hit test connection and it succeeded. I also tested the connection using .UDL and it worked. But when I try to access a .dbf file using the query it returns empty results

Select * from OpenQuery(DBFFILESERVER, 'SELECT * from MyTable')

It is throwing the following message

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Advantage.OLEDB.1" for linked server "DBFFILESERVER" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Advantage.OLEDB.1" for linked server "DBFFILESERVER".

The same DBF file Database can be accessed using Advantage OLE from a SQL Server 2005 instance that we have on a different server (windows 2003). I am just trying to set up an environment on our new Windows 2008 Server but facing this issue.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

when it comes to linked servers I find the easiest approach is to create an ODBC connection first on the local server and making sure the connection is successful.  Then in SQL server linked servers you create a linked server using ODBC to the saved/named ODBC connection on the local server. Once the server is created, right click on it>script>select to>new query window. You will see how to connect to it. usually [linkedserver].[dbname].[table]

you can try the script to new query window on your existing server you created to see if you can get any results.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if 'Ad Hoc Distributed Queries' it's enabled.
You can check that by running the following command: EXEC sp_configure 'Ad Hoc Distributed Queries'

If returned 0 means that isn't enabled so you need to enable it. like this:
EXEC sp_configure 'Ad Hoc Distributed Queries',1

Open in new window

I've been trying to implement 64 bit Advantage OLE DB driver in MS SQL Server with no success a few years ago... There was some security issue with the driver calls which I've been unable to fix.

The only solution seems to be the Sybase support which could know about some successful installation if any.

My working solution is VFP OLE DB driver installed on 32 bit MS SQL 2008 server. It works for queries but inserts and updates are always executed three times...

So the most reliable way how to process DBF files on MS SQL is to export DBFs from FoxPro into the MS SQL database via SQL Server ODBC driver or via SQL Native client.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Angel02Author Commented:
I created an ODBC connection on the server that has the .dbf files. Now I am trying
" Then in SQL server linked servers you create a linked server using ODBC to the saved/named ODBC connection on the local server"
How do I created a linked server using ODBC? Can you please give me the steps?

I tried to script->select to but the only option highlighted in script is "create to" and "drop to". i don't have select to.

@Vitor Montalvão
Ad Hoc Districuted queries was disabled. I enabled it but it still throws the same error.

"So the most reliable way how to process DBF files on MS SQL is to export DBFs from FoxPro into the MS SQL database via SQL Server ODBC driver or via SQL Native client."
Can you please give me more details?
"I tried to script->select to but the only option highlighted in script is "create to" and "drop to". i don't have select to."

on which object did you righ-click on? it needs to be on one of the linked tables. You should be able to drill down your linked server>catalogs>dbs>tables then right click on the table and script to a new query window.

If you can't drill down then you probably set something wrong or there's a permission issue, or being able to visually see the linked tables may not be supported for the provider in sql server.

"I created an ODBC connection on the server that has the .dbf files"

The dbf files can be remote but the ODBC connection needs to be created on the server that has sql server.
Did you do this? also it's preferable to be a System DNS not user.

Then on the local SSMS of sql server, navigate to linked server, right-click and create a new linked server.
In the new linked server window:

1.Linked server: enter any name without spaces or special chars. eg. LINKSERV1
2.Choose Other data source
3.Provider: Microsoft OLE DB Provider for ODBC Drivers
4.Product name: any name you want e.g LINKSERV1
5.Data source: The system DSN name of the ODBC connection you saved on the local machine. e.g LINKSERV1

save. right click on the linked server and Test the connection. I will tell you more if you get this far.

good luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angel02Author Commented:
I created the ODBC on teh server that had SQL (It's my development server and both dbf files and SQL are on the same server)
I followed your instructions and tried to create a linked server using the ODBC connection but I am getting the attached error.

Also, on this server the previous linked servers that I created using Advantage database server, they say 'connection succeeded' but when I expand the catalogs, there is nothing.
Did you get a successful test connection after you created the system odbc in windows? Can you show me a screenshot of windows odbc name you created ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also a screenshot of your Linked Server configuration will be helpful.
Angel02Author Commented:
How do I test the ODBC conenction in Windows that I created to access dbf files?

Please  see the screenshots attached.
Vitor MontalvãoMSSQL Senior EngineerCommented:
But you are trying to connect to Advantage or DBase?
Angel02Author Commented:
I am trying both to see whichever works. I got connected to the DBF files using Advantage provider but cannot view the tables.
Now I created an ODBC connection to those DBF files. In the list of drivers, I could only find 'Microsoft DBase driver' to connect to DBF files.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to install Advantage client to have access to his drivers.
Angel02Author Commented:
I tested the ODBC connection in windows (TESTODBC from screenshot above) using .UDL and it succeeded.
Steps to upload DBF table from FoxPro to SQL Server:
a) MS Visual FoxPro 9 SP2  or  VFP 9 run-time libraries + VRunFox9 emulator
b) MS SQL ODBC driver or SQL Native client or other suitable MS SQL driver installed

The MS Visual FoxPro 9 is a part of MSDN subscription, SP2 is available on the MSFT web.
VFP 9 SP2 run-time libraries are available here: (they have also been in but this site is not available any more).
VRunFox9 is a small open source FoxPro application which emulates the VFP Command Window: (source is available at GitHub)

MS SQL Server database requirements:
Nothing special. It is good to have the table ready for upload otherwise you have to create it on the fly. Both table name and column names can be same as the ones used in DBF file.

Upload code:
LOCAL laFlds[1], lnFlds, lcInsertCmd, lnI, lcMSSQLTable, lnMSSQLHandle, laErr[1]

*-- Connect to the SQL Server
lnMSSQLHandle = SQLSTRINGCONNECT("<YourConnectionString>")   &&  See connection strings at
IF lnMSSQLHandle < 0
  *-- Connection not successful - display an error
  = AERROR(laErr)

IF USED('a1')
  USE IN a1

USE <YourDBFtable> ALIAS a1    &&   Insert your DBF file name and path to this command
lcMSSQLTable = "<YourMSSQLTableName>"    &&   Use your SQL Server table name

*-- Build INSERT command
lnFlds = AFILEDS(laFlds)

lcInsertCmd = 'INSERT INTO ' + m.lcMSSQLTable + '('

FOR lnI = 1 TO m.lnFlds
  lcInsertCmd = m.lcInsertCmd + IIF(m.lnI>1,',','') + laFlds[m.lnI, 1]  && here you can generate quoted identifiers as well

lcInsertCmd = m.lcInsertCmd + ') VALUES ('

FOR lnI = 1 TO m.lnFlds
  lcInsertCmd = m.lcInsertCmd + IIF(m.lnI>1,',','') + '?a1.' + laFlds[m.lnI, 1]

lcInsertCmd = m.lcInsertCmd + ')'

*-- Loop throught the DBF table and insert records to SQL Server
  IF SQLEXEC(m.lnMSSQLHandle, m.lcInsertCmd) > 0
    WAIT WINDOW 'Row ' + TRANSFORM(RECNO()) + ' inserted' NOWAIT  && Display progress
    ? 'Error in ' + TRANSFORM(RECNO())    &&  STOP on error (you may call AERROR to know more about it)


Open in new window

FoxPro commands and functions are described in the VFP help file:

SQLSTRINGCONNECT() has all the connection parameters defined in the connection string but it may be replaced by SQLCONNECT() which reads the previously defined DSN (You may create DSN in ODBC administrator which is located in Windows\SysWOW64\ODBCAD32.EXE)

The updated FoxPro code must be compiled first and then you may run it. Following commands executed in the FoxPro Command Window will do it:

DO YourCode.FXP

Good luck!
Angel02Author Commented:
I edited the properties of Advantage provider and check 'Allow inProcess'. I am now able to access the DBF tables using a linked server with Advantage Provider.
it would be great if I could also get it to work with ODBC connection like mentioned in this thread.

I don't have FoxPro. The dbf files I am talking about are from a clipper program. I am not sure if I can use your solution. Also these tables have to be read and updated at regular intervals and it is not a one-time import.

@Vitor Montalvão
I installed Advantage Client but I cannot see its drivers while creating the ODBC connection.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Angel02, are you working in a 64bits system?
If so, maybe you are using the ODBC 32bits version instead of the 64bits. Please check that.
Angel02Author Commented:
I am using 32-bit window server  and 32 bit advantage client.
You don't need FoxPro but somebody who will create a small EXE application which you can then execute at regular time intervals in e.g. Windows Task scheduler.

But I agree it means some extra effort to make this solution working. OTOH, if you need to update DBFs from SQL Server data then the small FoxPro or Clipper app is the most reliable way.

Of course, you should not forget the fact DBF format becomes deprecated in Microsoft...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.