本文介绍如何使用 VFP ODBC 驱动程序或 VFP OLE DB 提供程序执行 SQL Server 分布式查询，以从 FoxPro .dbc 和 .dbf 文件检索数据。
原始产品版本： Visual FoxPro
原始 KB 数： 207595
总结
本文演示如何执行 SQL Server 分布式查询，以使用 VFP ODBC 驱动程序或 VFP OLE DB 提供程序从 FoxPro .dbc 和 .dbf 文件检索数据。
详细信息
Microsoft SQL Server 2000 提供对 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 提供程序与 FoxPro 配合使用。 VFP ODBC 驱动程序不是线程安全的。 由于 SQL Server 是多线程的，因此 VFP ODBC 驱动程序在某些情况下可能会导致问题。 如果有可能的话，建议使用 VFP OLE DB 提供程序连接到 SQL Server 数据。
以下 T-SQL 代码示例演示如何使用 OpenQuery 和 OpenRowset 函数在 FoxPro 中设置和使用分布式查询。 它还演示如何从 SQL Server 2000 更新远程 FoxPro 表。 在 SQL Server 2000 计算机上安装 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"?
-- SQL Server's QUOTED_IDENTIFIER has to be set to OFF.
SET QUOTED_IDENTIFIER OFF
-- OPENROWSET DSN-less example
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country = "USA" order by city')
go
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
go
Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where city = "Seattle"')
set region = "WW"
go
-- check to verify which rows were updated
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WW"')
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:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country = "USA" order by city')
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 city = "Seattle" ')
go
-- We will set the region back to "WA" if it currently is "WW".
-- Update using OpenQuery
Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ')
set region = "WA"
go
-- Make sure that the region got updated.
SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "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:\VFP90\samples\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 city')
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:\VFP90\samples\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:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where city = "Seattle"')
set region = "WW"
go
-- verify update
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where region = "WW"')
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:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
还可以使用 Visual FoxPro OLE DB 提供程序创建分布式查询。 这是使用的首选技术。 使用此代码可以演示如何更新和删除数据，但不支持在使用 OLE DB 访问接口的分布式查询中添加、更新（编辑）和删除数据。
以下 T-SQL 代码示例演示如何使用 OpenQuery 和 OpenRowset 函数设置和使用与 FoxPro 的分布式查询。 在 SQL Server 2000 计算机上安装 Visual FoxPro OLE DB 提供程序后，可以在 SQL Query Analyzer 中测试此代码。 需要根据需要更改 FoxPro 文件的数据源名称和路径：
'/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
'Modify your code accordingly for differences in location or DBC name */
--*====================================================
--* Using the DBC file, reading and updating data.
--*====================================================
--* A couple of OPENROWSET queries.
select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where country != "USA" order by country')
go
Select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where region="WA"')
go
--* Need to use an error trapping routine with the UPDATE and DELETE functions:
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Update Customer Set city = "SEATTLE" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
-- check to verify which rows were updated
select * from openrowset('VFPOLEDB',
'E:\VFP7junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'select * from customer where region = "WA"')
go
--* Change the City field back to "Seattle".
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Update Customer Set city = "Seattle" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
--* The DELETE fucntion also causes an error, but the DELETE works.
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Delete from Customer where country = "Spain" ')
go
declare @delerror int
select @delerror = @@error
print ''
if @delerror != 7357 and @delerror != 0
print 'Delete failed with error '+convert(varchar(5),@delerror)
else
print 'Ignore the error above, the Delete succeeded'
go
--* Check to see that the records are deleted.
Select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where country = "Spain"')
go
--* Here are some examples using the VFP OLE DB Provider to create Linked Servers.
--* Using sp_addlinkedserver to create the Linked Server.
sp_addlinkedserver @server='VFP_Linked_Server',
@srvproduct='Microsoft Visual FoxPro OLE DB Provider',
@provider='VFPOLEDB',
@datasrc = 'E:\vfp7junk'
go
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "Seattle"')
go
-- The Update command will update the table with the OPENQUERY function when using the
-- linked server, but the same error 7357 error will occur.
select * from
OPENQUERY([VFP_Linked_Server],
'Update Customer Set city = "SEATTLE" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
-- Check and see if the City field is all uppercase with "SEATTLE".
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where region = "WA"')
go
--* Let's check for how many records have the word "London" in the City field.
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "London"')
go
-- We can also use the Delete command to remove records with the OPENQUERY function when using the
-- linked server, but the same error 7357 error will occur.
select * from
OPENQUERY([VFP_Linked_Server],
'Delete from Customer where city = "London"')
go
declare @delerror int
select @delerror = @@error
print ''
if @delerror != 7357 and @delerror != 0
print 'Delete failed with error '+convert(varchar(5),@delerror)
else
print 'Ignore the error above, the Delete succeeded'
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:\VFP8junk\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by country')
go
参考
有关设置和使用分布式查询的更多详细信息，请查看 sp_addlinkedserverSQL 7.0 联机丛书中的 OpenQuery、OpenRowset 和相关主题。
若要了解有关 FoxPro 和.dbf.dbc文件的详细信息，请参阅 FoxPro 产品文档。