>Can I set up a linked server in SQL server that works on Foxpro for DOS tables ?
>
>I suppose I have to use the 'oledb provider for ODBC driver' but don't know what to fill in in
>- Product name
>- datasource
>- providerstring
>- Location
>- Catalog
Pascal,
As Sergey showed you could add it as a linked server. With SQL2000 and later there is another option which I like. It doesn't add foxpro table as a linked server but uses it on demand with OpenRowSet() function. From SQL help :
"Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one."
ie:
Create Table myCountries (country c(10))
Insert into myCountries values ('USA')
Insert into myCountries values ('Germany')
Insert into myCountries values ('Canada')
use
lnHandle=SQLStringConnect('DRIVER=SQL Server;'+;
'SERVER=server;Trusted_connection=Yes')
SQLExec(lnHandle, ;
"select * from Northwind.dbo.customers as rmtR"+;
" inner join OPENROWSET('VFPOLEDB.1', "+;
" '"+Sys(5)+Curdir()+"';'';'',"+;
" 'select * from myCountries') as locR"+;
" on rmtR.country = locR.country",'myResult' )
SQLDisconnect(lnHandle)
Cetin