概述

本文演示如何執行一個 SQL Server 分布式查詢來從 FoxPro .dbc 和 .dbf 文件獲取數據.

更多信息

Microsoft SQL Server 7.0 提供了執行基于 OLE DB providers 的查詢的能力. 這是靠使用 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 driver 來設置一個已連接服務器 (linked server) 來執行基于 FoxPro .dbc 和 .dbf 文件的分布式查詢. 不支持用 Jet OLEDB Provider 和 FoxPro.

以下 T-SQL 示例代碼演示如何用 OpenQuery 和 OpenRowset 函數設置和使用分布式查詢 FoxPro. 它也演示了如何從 SQL Server 更新一個遠程 FoxPro 表. 你可以在一個 SQL Server 7.0 機器上安裝了 Visual FoxPro ODBC driver 后, 在 SQL Query Analyzer 中測試這些代碼 . 你需要改變數據源名和路徑到適當的 FoxPro 文件:


/* OPENROWSET 和 OPENQUERY 示例. 以 VFP 通過 ODBC OLE DB provider */

/* 這些 OPENROWSET 示例依賴于示例文件 VFP98\data\Testdata.dbc
如果你的數據在不同的位置請修改你的代碼 */

--====================================================
-- 使用 DBC 文件 , 讀和更新
--====================================================
-- OPENROWSET DSN-less 示例

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 示例
/* 注意：如果 SQL Server 是配置為使用本地帳號 DSN 示例可能失敗.*/
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 示例 */
-- sp_addlinkedserver example with DSN

/* 你需要生成一個 DSN 并使它指向 Testdata 數據庫.
修改你的代碼以反映 DBC 位置 */

/* 注意：如果 SQL Server 是配置為使用本地帳號 DSN 示例可能失敗.*/
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

--====================================================
-- 使用 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