VFP Linked Server problems

Question

VFP Linked Server problems

Kevin Olson

Valued Member

Points: 53
More actions
November 22, 2005 at 12:08 pm

#68627

We're using SQL Server for the majority of our applications, but our accounting package is still in VFP 8.0. I need to make some costing adjustements from our inventory program. I have linked the VFP database to SQL Server just fine and, using OPENQUERY, I can SELECT data without a hitch. Performing an UPDATE or INSERT, however, is a different story. I get the following error:
"Server: Msg 7301, Level 16, State 2, Line 1
Could not obtain a required interface from OLE DB provider 'VFPOLEDB'."
Scouring the internet wasn't very helpful on this. Can anyone offer me any suggestions?
November 25, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Crowhurst SSC Enthusiast Points: 148 More actions · Answer 1

Hi,

How have you created your linked server? I am having problems using linked servers as well. In my scenario I have created some SPs and functions on the server which access a VFP.

I have no problems using simple OPENQUERY() statements, but whenever I access the SPs via ADO on a client machine I getting the following error:

OLE IDispatch exception code 0 from Microsoft OLE DB Provider for SQL Server: OLE DB provider "VFPOLEDB" for linke server "companydb" returned message "Invalid path or filename..."

However if I run the SPs directly from SQL2005, they work fine.

Any ideas?

Terran Cheung Grasshopper Points: 11 More actions · Answer 2

It seems that VFPOLEDB only support queries when it is used via linked server.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp/html/dggrfoledbproviderforvisualfoxpro.asp

Ian Yates SSCoach Points: 19738 More actions · Answer 3

That article states that via linked servers (ie 4 part name in SQL Server) you can only do selects. However I am reasonably sure that you can do updates, inserts and deletes via openquery - the trick may be that you have to do something like

insert into openquery(..., ....)

values(........)

Or the alternative may be

select * from openquery('my insert statement')

which would return zero records.

I haven't tried either approach but one or both of them may work. Also note that openquery does not take variable arguments, so to execute an update statement that you have built yourself in a varchar variable you will need to use dynamic SQL to execute the openquery statement.

Kevin Olson Valued Member Points: 53 More actions · Answer 4

Well, although there's a chance I didn't phrase the update statement correctly, I did attempt to perform an openquery update. SQL Server kindly gave me a rather nebulous error message.

Ian Yates SSCoach Points: 19738 More actions · Answer 5

as it is want to do

From this Q&A (which is poorly formatted / written) at http://www.eggheadcafe.com/aspnet_answers/dataoledb/May2006/post26836065.asp it appears that you can make your linked server and insert using

Insert into MY_VFP_LINKED_SERVER.TABLE_NAME(col1, col2, col3)

values(....)

Can you post the SQL code you are using for the insert?