如何用语句将DBF库导出，导入SQLSERVER2000中
如何用语句将DBF库导出，导入SQLSERVER2000中,我知道的只有TXT文件可以，不知那位高手知道？？？？？？？ 问题点数：20、回复次数：10Top
1 楼pengdali（）回复于 2003-09-01 12:42:16 得分 10
SUMMARY
This article demonstrates how to perform a SQL Server distributed query to retrieve data from FoxPro .dbc and .dbf files.
MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name.
For example:
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
You should use the Microsoft OLE DB provider for ODBC (MSDASQL) and the Visual FoxPro ODBC driver to set up a linked server to perform distributed queries against FoxPro .dbc and .dbf files. Using Jet OLEDB Provider with FoxPro is not supported.
The following T-SQL code example demonstrates how to set up and use distributed queries with FoxPro with OpenQuery and OpenRowset functions. It also demonstrates how to update a remote FoxPro table from SQL Server 7.0. You can test this code in SQL Query Analyzer after you install the Visual FoxPro ODBC driver on a SQL Server 7.0 machine. You will need to change the data source names and path to FoxPro files as appropriate: /* 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
REFERENCES
For more details on setting up and using Distributed Queries, take a look at sp_addlinkedserver, OpenQuery, OpenRowset and related topics in SQL 7.0 Books Online.
To learn more about FoxPro, and .dbf and .dbc files, refer to the FoxPro product documentation.
Top
2 楼tj_dns（愉快的登山者）回复于 2003-09-01 13:34:32 得分 1
insert table1
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\fox";User ID=admin;Password=;
Extended properties=dBase 5.0')...table1
Top
3 楼tj_dns（愉快的登山者）回复于 2003-09-01 13:35:00 得分 2
insert table1
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\fox";User ID=admin;Password=;
Extended properties=dBase 5.0')...table1
Top
4 楼zjcxc（邹建）回复于 2003-09-01 15:03:31 得分 2
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceDB=c:\;SourceType=DBF',
'select * from [a1.DBF]')
Top
5 楼vclzy（浪剑式）回复于 2003-09-02 14:33:04 得分 0
我执行下面代码怎么报错呢？我已经配置好ODBC了
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceDB=c:\;SourceType=DBF',
'select * from h.DBF')
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MSDASQL' 报错。
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'h.dbf' does not exist.]
OLE DB 错误跟踪［OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ］。Top
6 楼zjcxc（邹建）回复于 2003-09-02 14:41:51 得分 1
你的文件不存在啊.检查你的文件.Top
7 楼zjcxc（邹建）回复于 2003-09-02 14:43:42 得分 1
如果你的文件不是:c:\h.dbf
要对应修改目录和文件名:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceDB=c:\;SourceType=DBF',
'select * from h.DBF')
Top
8 楼vclzy（浪剑式）回复于 2003-09-02 15:11:46 得分 0
文件有，我用FOX都打的开，哦对了我是通过PC访问服务器上的SQL，我这装的是客户端
ODBC也是本地配的，有影响吗？Top
9 楼zjcxc（邹建）回复于 2003-09-02 16:24:03 得分 2
文件必须和SQL在同一台电脑上.Top
10 楼vclzy（浪剑式）回复于 2003-09-03 10:22:04 得分 0
在客户端不行吗，我怎么看有的程序可以实现，请高手指教Top
11 楼zhbname（赤澜）回复于 2003-09-03 15:55:28 得分 1
upTop
