We help IT Professionals succeed at work.

Linked Server to connect using Advantage OLEDB from MS SQL

3,431 Views
Last Modified: 2014-09-08
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.
Comment
Watch Question

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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
@presequel_server
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.

@pcelba
"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?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
@prequel_server
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.
ErrorEE.jpg
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Also a screenshot of your Linked Server configuration will be helpful.

Author

Commented:
How do I test the ODBC conenction in Windows that I created to access dbf files?

Please  see the screenshots attached.
ODBC-EE.jpg
LinkedServer-EE.jpg
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
But you are trying to connect to Advantage or DBase?

Author

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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You need to install Advantage client to have access to his drivers.

Author

Commented:
@prequel_server
I tested the ODBC connection in windows (TESTODBC from screenshot above) using .UDL and it succeeded.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
@All
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.

@pcelba
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I am using 32-bit window server  and 32 bit advantage client.
CERTIFIED EXPERT

Commented:
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...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.