[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2670
  • Last Modified:

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.
0
Angel02
Asked:
Angel02
  • 7
  • 5
  • 3
  • +1
3 Solutions
 
prequel_serverCommented:
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.
0
 
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
 RECONFIGURE WITH OVERRIDE
GO

Open in new window

0
 
pcelbaCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Angel02Author 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?
0
 
prequel_serverCommented:
"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
0
 
Angel02Author 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
0
 
prequel_serverCommented:
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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also a screenshot of your Linked Server configuration will be helpful.
0
 
Angel02Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
But you are trying to connect to Advantage or DBase?
0
 
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to install Advantage client to have access to his drivers.
0
 
Angel02Author Commented:
@prequel_server
I tested the ODBC connection in windows (TESTODBC from screenshot above) using .UDL and it succeeded.
0
 
pcelbaCommented:
Steps to upload DBF table from FoxPro to SQL Server:
Prerequisites:
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: http://www.foxpert.com/runtime.htm (they have also been in archive.msdn.microsoft.com but this site is not available any more).
VRunFox9 is a small open source FoxPro application which emulates the VFP Command Window: http://leafe.com/dls/vfp (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 www.connectionstrings.com
IF lnMSSQLHandle < 0
  *-- Connection not successful - display an error
  = AERROR(laErr)
  DISPLAY MEMORY LIKE laErr
  RETURN
ENDIF

IF USED('a1')
  USE IN a1
ENDIF

SELECT 0
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
NEXT

lcInsertCmd = m.lcInsertCmd + ') VALUES ('

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

lcInsertCmd = m.lcInsertCmd + ')'

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

= SQLDISCONNECT(0)

Open in new window


FoxPro commands and functions are described in the VFP help file: https://vfpx.codeplex.com/releases/view/23319

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:

COMPILE YourCode.PRG
DO YourCode.FXP

Good luck!
0
 
Angel02Author 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.
0
 
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.
0
 
Angel02Author Commented:
I am using 32-bit window server  and 32 bit advantage client.
0
 
pcelbaCommented:
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...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now