>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

Steve,

Your way works perfectly. It's a shame that the syntax is so long-winded. Ideally, we'd be able to create a linked server and then write our queries as if it were just another SQL Server. Your solution works, though, and I'll probably stick with it unless I can find some way to get the four-part syntax to work. Thanks much.

Richard