Very Computer

I've set up a linked server in MS SQLServer 7 using the new Foxpro 7
OLE DB provider (vfpoledb.dll).  I can read from the linked server
with no problem however I tried every way I can think of to write
through the linked server and can't get it working.  I'm current with
SQLServer patches and MDAC versions. What follows are the various
attempts I've made to no avail.

Any assistance would be greatly appreciated.

Create the linked server.
*****************************
Exec  sp_addlinkedserver






               Data Source=G:\LTF\TMS\Testw.dbc;Mode=ReadWrite|Share
Deny None',

Using four-part name format
********************************
UPDATE MyTest...people SET name='Fred' WHERE name='Bill'

Server: Msg 7333, Level 16, State 2, Line 85
Could not fetch a row using a bookmark from OLE DB provider
'VFPOLEDB'.
[OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No
work was done.]

Using OPENQUERY
note: An error occurs however the recordset is updated.
********************************

SELECT * FROM OPENQUERY(Mytest, 'UPDATE people SET name = "Fred"
      WHERE name="Bill"') TempDB

Server: Msg 7357, Level 16, State 2, Line 53
Could not process object 'UPDATE people SET name = "Fred"
WHERE name="Bill"'. The OLE DB provider 'VFPOLEDB' indicates
that the object has no columns.

Using a cursor
*******************************
DECLARE MyCursor CURSOR LOCAL SCROLL KEYSET OPTIMISTIC
   FOR SELECT * FROM MyTest...people WHERE name='Bill'
OPEN MyCursor
FETCH NEXT FROM MyCursor

UPDATE MyTest...people SET name='Fred'
FROM MyTest...people
WHERE CURRENT OF MyCursor

Server: Msg 16933, Level 16, State 2, Line 122
The cursor does not include the table being modified
or the table is not updatable through the cursor.

See if the following KB helps:

   Q207595 - HOWTO: SQL Server 7.0 Distributed Query With FoxPro .dbf Files

May want to make sure that your SQL Server service account has 'write' privileges to the .dbf file.
Also by turning on trace flag 7300, you may be able to get more details on the error message when executing the UPDATE.
To enable trace flag, execute DBCC TRACEON(-1,7300)
HTH!
-Keith
=================================================
*  This posting is provided "AS IS" with no warranties, and confers no rights.
*  Please do not send email directly to this alias.Respond to newsgroup

--------------------

|>Newsgroups: microsoft.public.sqlserver.programming
|>Subject: SQLServer linked server and Foxpro OLEDB provider
|>Date: 8 Nov 2001 12:27:59 -0800
|>
|>I've set up a linked server in MS SQLServer 7 using the new Foxpro 7
|>OLE DB provider (vfpoledb.dll).  I can read from the linked server
|>with no problem however I tried every way I can think of to write
|>through the linked server and can't get it working.  I'm current with
|>SQLServer patches and MDAC versions. What follows are the various
|>attempts I've made to no avail.
|>
|>Any assistance would be greatly appreciated.
|>
|>
|>Create the linked server.
|>*****************************
|>Exec  sp_addlinkedserver






|>               Data Source=G:\LTF\TMS\Testw.dbc;Mode=ReadWrite|Share
|>Deny None',

|>
|>
|>
|>
|>
|>
|>Using four-part name format
|>********************************
|>UPDATE MyTest...people SET name='Fred' WHERE name='Bill'
|>
|>Server: Msg 7333, Level 16, State 2, Line 85
|>Could not fetch a row using a bookmark from OLE DB provider
|>'VFPOLEDB'.
|>[OLE/DB provider returned message: Multiple-step OLE DB operation
|>generated errors. Check each OLE DB status value, if available. No
|>work was done.]
|>
|>
|>
|>
|>
|>Using OPENQUERY
|>note: An error occurs however the recordset is updated.
|>********************************
|>
|>SELECT * FROM OPENQUERY(Mytest, 'UPDATE people SET name = "Fred"
|>      WHERE name="Bill"') TempDB
|>
|>Server: Msg 7357, Level 16, State 2, Line 53
|>Could not process object 'UPDATE people SET name = "Fred"
|>WHERE name="Bill"'. The OLE DB provider 'VFPOLEDB' indicates
|>that the object has no columns.
|>
|>
|>
|>
|>
|>
|>Using a cursor
|>*******************************
|>DECLARE MyCursor CURSOR LOCAL SCROLL KEYSET OPTIMISTIC
|>   FOR SELECT * FROM MyTest...people WHERE name='Bill'
|>OPEN MyCursor
|>FETCH NEXT FROM MyCursor
|>
|>
|>UPDATE MyTest...people SET name='Fred'
|>FROM MyTest...people
|>WHERE CURRENT OF MyCursor
|>
|>
|>Server: Msg 16933, Level 16, State 2, Line 122
|>The cursor does not include the table being modified
|>or the table is not updatable through the cursor.
|>