2021腾讯云限时秒杀，爆款1核2G云服务器298元/3年！（领取2860元代金券），
地址：https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起)，
入口地址：https://www.aliyun.com/minisite/goods

推荐：DBF表与SQL Server表的导入、导出

----------Dbf 导入 Sql Server表---------- 以下均以SQL2000、VFP6及以上的表为例 代码导入：查询分析器中执行如下语句(先选择对应的数据库) -------------如果

We're migrating our legacy system based Visual FoxPro to Java, and we need to configure the SQL Server to CRUD the DBF files of the system, because we'll rewrite the system in parts. So the employees will use both interfaces in the same time and we need real-time updates in both systems.

Right now, I'm able to INSERT and SELECT data on SQL Server but I can't UPDATE and DELETE.

I've run the following command to create the linked server:

sp_addlinkedserver @server = 'DEN',
@srvproduct = 'foxpro',
@provider = 'VFPOLEDB.1',
@datasrc = 'D:\BaseTeste\denny\denny_db.dbc'

And run the following SQL to update a table:

UPDATE DEN...produtos SET familia=1 WHERE id=35

And I've received this error:

OLE DB provider "VFPOLEDB" for linked server "DEN" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7333, Level 16, State 2, Line 1

Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "DEN".

How to solve that? Thanks.

sql-server dbf visual-foxpro
  |
  this question asked Feb 27 '12 at 14:26 Danniel Magno 105 1 2 13

---

---

 | 

3 Answers
3

解决方法

I use the VFP OleDB regularly, and have no problem doing ANY inserts, updates, deletes, selects.... One thing to note. Your connection string can either point to a directory where the tables are located. In addition, the database can be included if a specific .DBC is associated with the tables in question.

When running a query, insert, update or delete, you do not need to qualify the database such as

DEN....produtos (which I assume is meant to be Denny_db.Produtos -- thus indicating Database.Table to run query against). Don't do that... the database is open and "visible" from the connection.... you should just be able to do....

推荐：DBF表与SQL Server表的导入、导出（转载）

以下均以SQL2000、VFP6及以上的表为例 代码导入：查询分析器中执行如下语句(先选择对应的数据库) -------------如果接受导入数据的SQL表已存在 --如果接受导入数

Update Produtos set x = 1 where something = whatever

or

insert into Produtos (fld1, fld2, fld3) values ( something1, another2, last3)

Another thing about VFP, when the table is associated with a given database, as soon as it is opened, if the corresponding database is NOT opened, it will be FORCED open to utilize any triggers and such. So you COULD simplify your connection to just point to the path and let the rest of the stuff just WORK for you.

Another note.... if you have a directory structure that has paths under it with data in other locations, such as

C:\SomeFolder\MainDataPath\
C:\SomeFolder\MainDataPath\SomeArchives\
C:\SomeFolder\MainDataPath\OtherFolder\

and make your connection to just the "C:\SomeFolder\MainDataPath\" location, your queries can use relative path to get at data inside the other locations such as

select whatever 
   from SomeRootTable SRT
      join SomeArchives\SubFolderTable SFT
         on SRT.KeyID = SFT.LinkKeyID

  |
  this answer edited Jan 4 '14 at 9:40 Dhanish Jose 528 1 5 17 answered Feb 29 '12 at 17:14 DRapp 33.5k 9 46 104      Well, I can't access the database like you say, so I'm doing something wrong. When I execute UPDATE produtos SET familia=1 WHERE id=35 I get the following error Msg 208, Level 16, State 1, Line 1 **Invalid object name 'produtos'**. When I try select a database by USE DEN SQL Server returns this error Msg 911, Level 16, State 1, Line 1 **Could not locate entry in sys databases for database 'DEN'. No entry found with that name. Make sure that the name is entered correctly.**. Could you help me solve this problem? Thanks. –  Danniel Magno Feb 29 '12 at 19:49

---

---

 | 

You're out of luck with a linked server:

When you use Visual FoxPro OLE DB Provider as a SQL Server-linked server, only queries are supported. The Visual FoxPro OLE DB Provider does not support update, insert, or delete operations through a linked server.

http://msdn.microsoft.com/en-us/library/0xzsac67(v=vs.80).aspx

Instead try OPENROWSET with the MSDASQL provider and pass the fox-pro ODBC driver as the 2nd argument (Driver={Microsoft Visual FoxPro Driver})

  |
  this answer edited Nov 2 '12 at 16:18 Thomas G. Mayfield 5,670 2 22 41 answered Feb 27 '12 at 14:42 Alex K. 115k 17 159 203      I've executed the following statement: SELECT * FROM OPENROWSET( 'MSDASQL', 'Driver={Microsoft Visual FoxPro Driver}; SourceDB={D:\BaseTeste\denny\denny_db.dbc}; SourceType=DBC', 'SELECT * FROM produtos' ) And received this error: > Msg 7399, Level 16, State 1, Line 1 > The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error. > Msg 7303, Level 16, State 1, Line 1 > Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)". –  Danniel Magno Feb 27 '12 at 14:51      I use openquery, and the The updates statements actual works, it just throws an error that cannot be avoided. –  manit Feb 21 '14 at 19:39

---

---

 | 

Having VFPOLEDB installed with SQL Server Express 2012 32bit the following seems to be working:

SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','file')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','update file set n=2 where n=1')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\file.dbf';'';'','delete from file where n=2')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\db2.dbc';'';'','tab2')
SELECT * FROM OPENROWSET('VFPOLEDB','D:\dir\db2.dbc';'';'','update tab2 set somedate=ctod("12/30/2000") where n=1')

The only inconvenience is that update and delete statements result with error 7357 telling you that no rows to be returned. You can wrap it in try/catch block and ignore 7357 as an expected condition since actual statements were executed anyway. To use dynamic parameters it is possible to execute this through exec(@sqltext).

  |
  this answer answered Dec 4 '13 at 13:13 slar 51 1 2

---

---

 | 

推荐：SQL Server 2008导入DBF文件中的数据

1、创建新数据库， 如：TEST 2、任务-->导入数据。。。 3、选择适配器:Microsoft OLE DB Provider for Visual FoxPro 9.0 注：SQL 2008缺省安装中没有foxpro的数