Tutorials to .com

Tutorials to .com » Software » Vfp » Dbf of how to use VFP to SQL Server 7.0 Distributed Query

Dbf of how to use VFP to SQL Server 7.0 Distributed Query

Print View , by: iSee ,Total views: 43 ,Word Count: 2012 ,Date: Sun, 19 Apr 2009 Time: 1:39 AM


Overview

This article demonstrates how to implement a SQL Server distributed query from FoxPro. Dbc and. Dbf file access to data.

For more information

Microsoft SQL Server 7.0 provides the implementation of the OLE DB providers based on the ability to query. This is by the use of OpenQuery or OpenRowset Transact-SQL function or use of a server name has been connected to the implementation of four inquiries.

For example:

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source', 'location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY (mylinkedserver, 'select * from table1')

Can use Microsoft OLE DB provider for ODBC (MSDASQL) and Visual foxpro ODBC driver to set up a server is connected (linked server) to execute based on FoxPro. Dbc and. Dbf files distributed query. Do not support and Jet OLEDB Provider with FoxPro.

The following T-SQL sample code demonstrates how to use OpenRowset function OpenQuery and distributed query to set up and use FoxPro. It also demonstrates how to update SQL Server from a remote FoxPro table. You can in a SQL Server 7.0 machine installed Visual FoxPro ODBC driver after the SQL Query Analyzer to test the code. you need to change the data source name and path to the appropriate FoxPro documents:


/ * OPENROWSET and OPENQUERY example. To vfp through ODBC OLE DB provider * /

/ * These OPENROWSET examples depend on the sample files VFP98 \ data \ Testdata.dbc
If your data in a different location, please modify your code * /

--================================================ ====
- The use of DBC file, read and update
--================================================ ====
- OPENROWSET DSN-less example

select * from openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data \ Testdata.dbc;
SourceType = DBC ',
'select * from customer where country! = "USA" order by country')
go

select * from openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data \ Testdata.dbc;
SourceType = DBC ',
'select * from customer where region = "WA"')
go

Update openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data \ Testdata.dbc;
SourceType = DBC ',
'select * from customer where region = "WA"')
set region = "Seattle"
go

- Check to verify which rows were updated
select * from openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data \ Testdata.dbc;
SourceType = DBC ',
'select * from customer where region = "Seattle"')
go

- OPENROWSET DSN example
/ * Note: If SQL Server is configured to use a local account may fail DSN example .* /
select * from openrowset ( 'MSDASQL',
'DSN = Visual FoxPro Database;
SourceDB = e: \ VFP98 \ data \ Testdata.dbc;
SourceType = DBC ',
'select * from customer where country! = "USA" order by country')
go

/ * Sp_addlinkedserver examples * /
- Sp_addlinkedserver example with DSN

/ * You need to generate a DSN and point it Testdata database.
Modify your code to reflect the DBC position * /

/ * Note: If SQL Server is configured to use a local account may fail DSN example .* /
sp_addlinkedserver 'VFP Testdata database With DSN',
'',
'MSDASQL',
'VFP system DSN'
go

sp_addlinkedsrvlogin 'VFP Testdata database With DSN', FALSE, NULL, NULL, NULL
go

SELECT *
FROM OPENQUERY ([VFP Testdata database With DSN], 'select * from customer where region = "Seattle"')
go

- Update using OpenQuery
Update OPENQUERY ([VFP Testdata Database With DSN], 'select * from customer where region = "WA"')
set region = "Seattle"
go

/ * SP_addlinkedserver example with DSN-less connection * /

/ * This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name * /

sp_addlinkedserver 'VFP Testdata Database With No DSN',
'',
'MSDASQL',
NULL,
NULL,
'Driver = (Microsoft Visual FoxPro Driver); UID =; PWD =; SourceDB = e: \ VFP98 \ data \ Testdata.dbc; SourceType = DBC; Exclusive = No; BackgroundFetch = Yes; Collate = Machine;'
go

sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL
go

SELECT *
FROM OPENQUERY ([VFP Testdata Database With No DSN], 'select * from customer where country! = "USA" order by country')
go

--================================================ ====
- Using VFP 6.0 driver, read and update data from VFP sample dbf files
--================================================ ====

- OPENROWSET DSN-less example

select * from openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data;
SourceType = DBF ',
'select * from customer where country! = "USA" order by country')
go

- Perform UPDATE

Update openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data;
SourceType = DBF ',
'select * from customer where region = "Seattle"')
set region = "WA"
go

- Verify update

select * from openrowset ( 'MSDASQL',
'Driver = Microsoft Visual FoxPro Driver;
SourceDB = e: \ VFP98 \ data;
SourceType = DBF ',
'select * from customer where region = "WA"')
go


- OPENROWSET DSN example
- DSN points to the folder where. Dbf files are.
/ * Note this Example may fail if SQL Server is configured to use a local account .* /
select * from openrowset ( 'MSDASQL',
'DSN = Visual FoxPro Tables;
SourceDB = e: \ VFP98 \ data;
SourceType = DBF ',
'select * from customer where country! = "USA" order by country')
go


Visual FoxPro Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.