HOWTO:使用 FoxPro .dbf 檔案執行 SQL Server 7.0 分散式查詢


結論


本文件示範如何執行 SQL Server 分散式查詢,從 FoxPro .dbc 和 .dbf 檔案擷取資料。

其他相關資訊


Microsoft SQL Server 版本 7.0 提供對 OLE DB 提供者執行查詢的能力。藉由使用 OpenQuery 或 OpenRowset Transact-SQL 函數,或使用含有已連結之伺服器名稱的四部份名稱的查詢,即可執行這項查詢。


例如:


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


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


您應該使用 Microsoft OLE DB provider for ODBC (MSDASQL) 和 Visual FoxPro ODBC 驅動程式來設定已連結的伺服器對 FoxPro .dbc 和 .dbf 檔案執行分散式查詢。不支援使用 Jet OLEDB Provider with FoxPro。


下列 T-SQL 程式碼範例示範如何以具有 OpenQuery 和 OpenRowset 函數的 FoxPro 設定和使用分散式查詢。同時還示範如何從 SQL Server 7.0 更新遠端 FoxPro 資料表。在 SQL Server 7.0 電腦上安裝 Visual FoxPro ODBC 驅動程式之後,您可以在 SQL Query Analyzer 中測試這個程式碼。您必須變更正確的 FoxPro 檔案的資料來源名稱和路徑:
/* OPENROWSET and OPENQUERY examples with VFP via ODBC OLE DB provider */

/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
Modify your code accordingly for differences in location or DBC name */

--====================================================
-- Using 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 the DSN Example might fail if SQL Server is configured to use a local account.*/
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 will need to make a DSN and point it to the Testdata database.
Modify your code accordingly for differences in location or DBC name */

/* Note this Example may fail if SQL Server is configured to use a local account.*/
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<BR/>

-- 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

参考


如需有關設定和使用「分散式查詢」的詳細資訊,請參閱 sp_addlinkedserver、OpenQuery、OpenRowset 及〈SQL 7.0 線上叢書〉的相關主題。


若要進一步瞭解 FoxPro 和 .dbf 與 .dbc 檔案,請參閱 FoxPro 產品文件。

本文件是根據 Microsoft Knowledge Base 文件編號 Q207595 翻譯的。若要參考原始英文文件內容,請至以下網址: