using Linked Server to Insert/Update dBase table?

Last Post 07 Jul 2005 08:00 AM by jPrice. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jPrice
New Member
New Member

--
07 Jul 2005 08:00 AM
We have a 3rd party app on our server that maintans its data in an old dBase file. I wanted to view and update the data through our SQL Server if possible.

The Linked Server was created using the "Microsoft OLD DB Provider for ODBC Drivers" linking to an ODBC connection created using the driver "Microsoft dBase VFP Driver". The ODBC driver uses a "Free Table directory" database type.

The following statement works:

SELECT *
FROM OPENQUERY(linkedServerName, 'SELECT * FROM AD WHERE ID = ''0000056A''')

An insert statement, like this:

insert OPENQUERY(linkedServerName, 'SELECT id, balance, limit FROM AD where 1 = 0')
values('price_9', 10.00, 0)

gives me the following error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Cannot update the cursor.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].

I've tried 3 other dBase ODBC drives, but even the select fails for them.

One question is that the directory in which the files are located are read only for "everyone" while being rw for only selected Active Directory accounts. The account which the SQL Server runs under has all rights, but the account that I'm running the Query Analyzer statements does not. Is that a problem? What account is issuing the SQL against the actual table? Is the problem somewhere else?

The failed update statement looks like:

update OPENQUERY(pCounter, 'SELECT * FROM AD WHERE ID = ''0000056A''')
SET limit = 1

and the error is,

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Cannot update the cursor.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005: ].





Acceptable Use Policy
---