Connecting to FoxPro data

I was given this one. Right now I am familiar with how to do this in Access,
but SQL Server is what I need to use.
What does one have to do to connect SQL Server (right now 2000, but soon
2005) to a FoxPro free-table directory?
I would like to have this be a READ-ONLY data source as I do not want to
allow this data/indexes to be updated or modified in any way.
And, can these tables be mixed in with the SQL data tables in the same SQL
database? If not, how can a table in another database (this FoxPro database)
be referenced in T-SQL?
I'm willing to read, you can give links.
Thank you,
JulianHi Julian,
Since you want to "mix" the Fox tables with the SQL tables it sounds like
what you need is a linked server.
Here's some of what I've posted:
http://groups.google.com/group/micr...300fe71db9bb97e
Note that when you select data from a VFP linked server you need the three
dots:
Select SQLTable.* From SQLServerTable As SQLTable
Inner Join FoxLinkedServer...Customers As Customers
On SQLTable.ID = Customers.CustomerID
Also, the data source string in the example is for a FoxPro DBC or "database
container." For free tables you just point to the directory where they are
located:
@.datasrc=N'"C:\Temp\"'
Offhand I don't know how you would make the linked server read-only -
perhaps by making a view of the data and letting the users have read-only
access to the view. A SQL Pass-through query is always read-only but if you
want users to use something like MS Access for querying and reports then an
SPT query wouldn't work.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"stjulian" <stjulian@.discussions.microsoft.com> wrote in message
news:ONBfsyqqGHA.2232@.TK2MSFTNGP04.phx.gbl...
>I was given this one. Right now I am familiar with how to do this in
>Access, but SQL Server is what I need to use.
> What does one have to do to connect SQL Server (right now 2000, but soon
> 2005) to a FoxPro free-table directory?
> I would like to have this be a READ-ONLY data source as I do not want to
> allow this data/indexes to be updated or modified in any way.
> And, can these tables be mixed in with the SQL data tables in the same SQL
> database? If not, how can a table in another database (this FoxPro
> database) be referenced in T-SQL?
> I'm willing to read, you can give links.
> Thank you,
> Julian
>

Connecting to FoxPro & Exporting to Excel

I am currently in the process of converting some Access functionality to T-S
QL.
What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
manipulate data in a SQL table and then export that table to Excel.
Can anyone tell me if this is possible.Youc can create a linked server to a FoxPro DBC:
EXEC sp_addlinkedserver
@.srvproduct='',
@.server='VFP01',
@.provider='VFPOLEDB',
@.datasrc='C:\xyz.DBC'
(I'm assuming VFP7.0 or later with the OLEDB driver installed)
Then reference your Fox tables directly:
SELECT * FROM VFP01...table_name
For the export to Excel you'll probably have to use DTS, which is also
another option for the FoxPro data: import it rather than link it.
David Portas
SQL Server MVP
--|||Hi Chubbly,
Just to add, the FoxPro and Visual FoxPro OLE DB data provider is
downloadable from http://msdn.microsoft.com/vfoxpro/downloads/updates .
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Chubbly Geezer" <ChubblyGeezer@.discussions.microsoft.com> wrote in message
news:2AF3439C-E2E5-45C8-B5D2-1FFFF1B6CEC9@.microsoft.com...
>I am currently in the process of converting some Access functionality to
>T-SQL.
> What I need to do is connect to a FoxPro *.dbc, use the FoxPro data to
> manipulate data in a SQL table and then export that table to Excel.
> Can anyone tell me if this is possible.
>
>|||Thanks David, almost there I think. However with the following code I am
getting an error message. Also in Enterprise Manager my linked server is
showing no tables.
The VB ADO string I used to connect previously is this:
OperaConn.ConnectionString = "Driver=Microsoft Visual Foxpro Driver; UID=;
SourceType=DBC; SourceDB=i:\operaii\data\comp_W.dbc"
EXEC sp_dropserver 'OPERA_SERVER'
EXEC sp_addlinkedserver
@.server='OPERA_SERVER',
@.srvproduct='',
@.provider='VFPOLEDB',
@.datasrc='c:\temp\Comp_W.dbc'
Select * From OPERA_SERVER...ssale
--
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'OPERA_SERVER' does not contain table 'ssale'. The table
either does not exist or the current user does not have permissions on that
table.
Chubbly
"David Portas" wrote:

> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>|||That's worked actually thanks very much David.
The error I had was caused by the fact I copied the files I beleived I
needed to a temp directory for testing. There were obviously files missing
which I also needed.
Many thanks.
"David Portas" wrote:

> Youc can create a linked server to a FoxPro DBC:
> EXEC sp_addlinkedserver
> @.srvproduct='',
> @.server='VFP01',
> @.provider='VFPOLEDB',
> @.datasrc='C:\xyz.DBC'
> (I'm assuming VFP7.0 or later with the OLEDB driver installed)
> Then reference your Fox tables directly:
> SELECT * FROM VFP01...table_name
> For the export to Excel you'll probably have to use DTS, which is also
> another option for the FoxPro data: import it rather than link it.
> --
> David Portas
> SQL Server MVP
> --
>
>