SQL Linked server to VFP flat tables

[BeatriceC]

I have an Visual FoxPro application which uses flat tables.
I need to be able to query those tables using SQL Server 16 (64 bit).
I know VFP OLEDB Provider does not support 64bit.
It was suggested in another post that it may be possible to use Linked server on SQL 64bit to SQL 32bit and another linked server on SQL 32bit to the VFP flat tables.
I created the SQL 32bit linked server using VFPOLEDB.
I am receiving an error and not sure what I'm doing wrong.
"Cannot initialize the data source object of OLEDB provider VFPOLRDB...Returned message."Invalid Path or file name."" SQL error 7303.
I verified and there are no errors in the path. The linked server configured "Data Source: \\ServerName\ShareName\ApplicationFolder\TableFolder " All the free tables reside in the TableFolder.
Can I use a linked server using VFPOLEDB to connect to flat tables or does it need to be a VFP Database?
I should also clarify that I don't have SQL server experience and learning as I go on this setup.
Any help is greatly appreciated.

 

[Nigel Gomm]

This may be possible and others may have better ideas but without knowing how this is going to be used my initial reaction is to do things the other way round.....

That is, create a VFP .exe that uploads these vfp tables to native SQL server tables on a regular basis.

Then you'll be using very standard techniques and, ultimately i suspect, fewer moving parts.

It may also be easier to update the original VFP app that creates your tables to use SQL server tables instead?

hth

n

 

[Mike Lewis]

Nigel makes a good point. We really need to know more about what you want to achieve? Do you have an existing SQL Server app that needs to access the VFP data? If so, is this a one-off requirement, or something you will do on a regular basis? And does the SQL Server app need real-time access to the VFP data? Or will it be sufficient to upload periodic snapshots?

In general, it is easier for VFP to send data to SQL Server than it is for SQL Server to access VFP tables. But I disagree with Nigel when he says "It may also be easier to update the original VFP app that creates your tables to use SQL server tables instead". In most cases, that would be a significant amount work - but perhaps don't rule it out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[BeatriceC]

Thank you for the responses.
There are apps using the data. Currently the individual is accessing the flat table date at a 3 minute interval, so pretty frequently. He is using MS Access linked tables to access the tables. He has started receiving errors connecting to some of the tables and is also interested in migrating to SQL 64bit queries.
There are two solutions, as you mentioned, using VFP to push data to SQL or SQL connecting to the tables. The thought is if he con connect directly, he'll have the flexibility to just get any data as his needs grow.

The vendor is not supporting an update away from flat tables as it is quite an undertaking.

I'm would like to pursue the SQL connection to the flat tables option first. I'm just getting an error on the linked server connection.

 

[Chris Miller]

I think you need to allow access to the VFP flat table folder. An SQL Server service doesn't run on a domain account, so you have to give permissions to the directory to whatever system account is running the SQL Server (32bit) service. And also SQL Server runs in session 0 (without login), that's why any drive mapping to network shares won't be available to SQL Server, your flat tables have to be local to SQL Server.

OLEDB also supports a directory of flat tables, not just DBC, that's not the problem.

Chriss

 

[BeatriceC]

I think I follow.
I'm not sure how to grant permission to a non domain account to the flat table share.
Also, what do you mean by "session 0(without Login)" Is this a setting on th Linked Server?

 

[Chris Miller]

Session Id 0 is a Windows System topic, not an SQL Server topic and has nothing to do with any settings, it's always that way.

To give permissions to a system account works like giving permissions to any other user. You just have to be aware: Most system accounts running services, like "NT Authority\Local Service" are local accounts (Well, the name says it). Every computer has that account, but being a local account means there are as many local accounts as there are computers, it's not one (domain) account, it's as many local accounts with the same name as there are computers in the network. So giving permissions on a folder to that account will only give it to that computer's local account and not for the Local Service account from another server.

The best setup is to have the 32bit Server Edition on the file server with the DBF folder. Local access of data is always most performant and more important than needing to send back results over the network, it's unavoidable towards the end-user workstation clients, anyway.

If the DBF file server is not under your control, eg your company seperates services like file servers from database servers or the DBFs are on a Linux server, or any other reason not allowing to install the 32bit Server instance there, the simplest solution will be to run the 32bit SQL Server Instance under a domain account having the network and file permissions to access the DBFs (and to find the path). Your own Windows account, for example. You can not only see but also change the account running the SQL Server service under Services.msc

Chriss