使用 FoxPro .dbf 文件SQL Server分布式查询
本文介绍如何执行SQL Server分布式查询，以使用 VFP ODBC 驱动程序或 VFP OLE DB 提供程序从 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')
应使用适用于 ODBC 的 Microsoft OLE DB 提供程序 (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 查询分析器中测试此代码。 需要根据需要更改 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 查询分析器中测试此代码。 需要根据需要更改 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
References
有关设置和使用分布式查询的更多详细信息，请查看 sp_addlinkedserverSQL 7.0 联机丛书中的 、OpenQuery、OpenRowset 和相关主题。
若要详细了解 FoxPro 和 .dbf.dbc 和 文件，请参阅 FoxPro 产品文档。