Richard,

>>I've created a linked server on SQL7 with the following commands:

>>EXEC sp_addlinkedserver
>>@server = 'DBFSERVER' ,
>>@provider = 'MSDASQL',
>>@provstr = 'DRIVER=Microsoft FoxPro VFP
>>Driver(*.dbf);UID=;Deleted=Yes;Null=Yes;Collate=Machine
>>;BackgroundFetch=Yes
>>;Exclusive=No
>>;SourceType=DBF
>>;SourceDB=c:\vfptest',
>>@srvproduct='s'
>>go

>>But I cannot query these tables. No syntax seems to work. I've tried

>>select * from [dbfserver].[c:\vfptest]..[orders.dbf]
>>select * from [dbfserver]...[orders.dbf]
>>select * from [dbfserver]...[orders]
>>select * from [dbfserver].[c:\vfptest].dbo.[orders.dbf]
>>select * from [dbfserver].[c:\vfptest].dbo.[orders]

>>But all these return errors either that the file could not be found or
>>that the schema or catalog supplied are invalid. Does anyone have any
>>ideas?

Solution is as follows:

sp_addlinkedserver
'DBFSERVER','', 'MSDASQL', NULL, NULL, 'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=c:\vfptest\;SourceType=DBF;
Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;'


With VFP Queries you cannot use the four part syntax (i.e. servername.database.owner.table). You have to use the openquery command
This wil then allow queries such as :

SELECT * FROM OPENQUERY([DBFSERVER],'SELECT * FROM Orders')

NOTE: SELECT * FROM [DBFSERVER]...Orders WILL NOT WORK

If you're working with a dbc, the above syntax is the same, you just need to change the connection string to be the dbc name in the sourcedb and the sourcetype to dbc.

Joins can then be performed between SQL tables and Fox as follows:

[Assuming there is a field called order_id in both your VFP table and the SQL server table]

select sql7table.*, vfpdbf.* from
OPENQUERY([DBFSERVER],
'SELECT * from orders where order_id = 200 (for example)) as vfpdbf
join sql7server.database.owner.sql7table as sql7table on vfpdbf.order_)id = sql7table.order_id

It looks a bit long winded I know, but I spent about a week on this, and this is the best way I could find.

Let me know if this helps, or if you find a better way.

Cheers
Steve Lea