本文件示範如何執行 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 翻譯的。若要參考原始英文文件內容，請至以下網址：
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
回此頁最上方
