OpenRowset not working on a linked FoxPro Server 2

[JoeAtWork]

On an SQL Server 2000 database I've written a stored procedure that updates a record in an old FoxPro DBF file.

This stored procedure works on my test machine and on another SQL server I tested on. However, I get the following error when I try to create the stored procedure on the production system (also SQL Server 2000)

Server: Msg 7399, Level 16, State 1, Procedure proc_AddCustToScheduler, Line 90
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

It's rather puzzling as it works on the other two servers, which as far as I know are identical. I am using the same FoxPro driver in all systems (vfpoledb.dll, version 9.0.0.3504).

The trouble seems to be with the OpenRowset command on the following line:

Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\server\TestDATA;
SourceType=DBF',
'select * from Datadict')
SET Express = @NewID
WHERE [Table]='COMPANYS'

I experimented in Query Analyzer by doing another OpenRowset that just fetched records and got the same error.

Any ideas why it doesn't work on this particular server?

In the mean time, I think I will be comparing the SQL Server versions, services packs, and compare what's in the registry for vfpoledb.dll.

 

[gmmastros]

While you are at it, check the version you have for MDAC.

Instructions for doing that can be found here:

http://support.microsoft.com/kb/301202

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

When version of VFP is used to create that DBF?
Last ODBC driver for VFP is for VFP 6.
So maybe that tabl;e is created with some of the next versions of VFP.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JoeAtWork]

gmmastros - thanks for reminding me of ComponentChecker!

bborissov - I believe it was created with FoxPro 2.6a (whatever that means)

I should mention that I have an ASP.NET 2.0 web app on that server that currently uses the vfpoledb driver to insert records into the same database. So I think the error message I am getting is misleading at best. I'm suspecting it's an SQL Server or MDAC issue.

 

[bborissov]

VFPOLEDB is OK, it works with ALL versions of Fox. ODBC didn't . Fox 2.6a is Fox for DOS, so the ODBC should work with it.
Also check

http://support.microsoft.com/kb/295297

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JoeAtWork]

When running ComponentChecker on the production server, it can't match to a particular MDAC install. On the test server it matches to "MDAC 2.8 SP2 on Windows Server 2003 SP1". On the production server it just says "UNKNOWN".

What I find curious is that the file versions on the production server are higher than the test server. For example MSDASQL.DLL is version 2.82.3959 on the production server, but only 2.82.1830 on the test server.

I'm a little hesitant to reinstall MDAC on a server that seems better patched than the test server. What would you advise?

 

[gmmastros]

>> What would you advise?

I'm hesitant to give you advice. I've never had (or heard) of any problems with updating MDAC. However... it is a production server that you're talking about, so you do need to be careful.

Truth is... I'd rather not give you a recommendation on this because I would feel awful if you took my advice and it caused problems on your production server.

Personally, I would be curious to hear with mrdenny has to say about this issue.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[onpnt]

I advice reinstalling MDAC on the server. Over the years I've had to do this many times on several servers that had different functions. MDAC are very shared components and often will become corrupted given any third party intervention that may directly touch it. It could be anything from a office update that caused a problem to a anti-virus software corrupting dll's. I guess my point is from a server end point MDAC and for that matter several other shared data access dll's on windows.



[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[onpnt]

BTW..reinstalling and getting a corrupted MDAC install fixed can be a pain the rear. Good luck! I've spent long nights trying to get the beast going

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[JoeAtWork]

Well, I emailed the boss and advised that I think an MDAC reinstall is needed, and asked if he wants one of his own techs (I'm a subcontractor) to do it or myself.

I've never had problems with MDAC installs yet, but there's a couple important applications running on that server that are currently running fine, and my nightmare is that I'll break one or the other.