Setting up VFP as a Linked Server

[musik]

I'm trying to link a Visual FoxPro DB (Free table directory) to SQL Server (2K) as a "Linked Server". This will (would!) allow me to access the FP tables as though they were tables in SQL Server and (the whole goal) do joins against other datasources.

The problem is, when I try to use my ODBC DSN, it just says there are no tables, and any table I try to access I get "[whatevername].dbf does not exist".

BUT, in Visual Basic (for example), the ODBC DSN works _just fine_ and I can see all the tables and all of their contents.

Why can't SQL Server connect to this data source?

Any other ideas of how I can do a JOIN from VFP Data to ADSI other than by making both Linked Servers on a SQL box? (And I'm a VB/VC programmer... the VFP db is a third-party app over which I have no control. I can't modify it in any way and I only have (and only need) read-only access.)

Thanks much!!!

 

[tlbroadbent]

Does the SQL Server login account have access and permissions to the location of the VPF files? In VB you will be using your login. SQL Server will use the SQL login so the permissions may not be the same. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[musik]

This would be the account that the MSSQLServer Service is configured to Log on as?

I changed it to my personal login (it's a test environment, I can do that... live I'll create a separate one) and I get the same problem. It takes a lot longer now, which makes me think it's actually going to the directory and scanning it, but I still get the same result.

 

[musik]

I should probably mention that the VFP files are on a different machine, being accessed by \\machinename\share\path... >I< have permissions to access this share and thereby SQL should (since it's running as me), but perhaps it just can't do this for some reason? (Though, again, ADO in VB can...)

 

[tlbroadbent]

You should be able to link the VFP database. Have you read the following KB articles at Microsoft?

HOWTO: Add a FoxPro Database to SQL Server as a Linked Server

http://support.microsoft.com/support/kb/articles/Q199/1/31.ASP

HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro dbf Files

http://support.microsoft.com/support/kb/articles/Q207/5/95.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[musik]

I've read those articles repeatedly...

I finally got it to connect. It was, indeed, a rights issue... not the one I expected, but it was.

Thanks for your help!

 

[BigRetina]

Hi..
I have EXACTLY the same problem..
how did u solve urs?
I can not figure the rights issue correctly!..i am logging as sa!!

 

[musik]

In my situation, it was the account that the SQL Server service was running as. I had to switch it to an account that had permissions on the VFP machine (in another domain, but trusted). I believe it had been either had been running as SYSTEM or the account I had created for it to run as (SQLServer) did not have perms ont he VFP box. (It's been over a year!

Hope this helps...

 

[BigRetina]

Thanks
IT WORKED..I can see the tables in the enterprize manager!
BUT..
When I select from the Linked Server as follows :
SELECT * FROM MyLink...MDCTRN
it gives me the following error:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
When I use OPENROWSET as follows :
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=F:\Sysdata\HIK\MDC;
SourceType=DBF',
'select * from MDCTRN')
IT WORKS FINE!
what could be the problem NOW?
Thanks Again

 

[musik]

Ummm, well, in a needlessly messy implementation, SQL Server does not allow you to SELECT from a Linked Server. You have to use OPENQUERY:

SELECT * FROM OPENQUERY(Mylink, '
SELECT * FROM MDCTRN
')

(I usually make it multi-row like that, at least in Query Analyzer, to separate the ''s from the actual SQL text. Use &quot;'s to protect your strings in the SQL that's being sent to VFP. The ''s are to protect the VFP string from SQL Server.)

Why they felt the need to NOT make this transparent (and allow SELECT * FROM Mylink..MDCTRN) is beyond me. Perhaps they felt that making it transparent would hide an &quot;expensive&quot; implementation, making it a likely candidate for abuse. *shrug* If anyone knows, I'd love to know!

-Damien

 

[BigRetina]

but (SELECT * FROM MyLink...MDCTRN) is working FINE in SQL Server 7.0!!!

 

[musik]

Well, then I guess I have to reiterate my former question. Does anyone out there know why MS decided to do this implementation of linked servers???