August 3, 2009 at 3:20 am
We have a number of jobs that are intermittently failing. The common factor is that the job stages that fail use openrowsets similar to:
OPENROWSET('Microsoft.Jet.OLEDB.4.0', '\\server\accessdb.mdb';'user';'pwd',accessdb)
for working with access 95 (yes 95, don't ask....) databases.
This started happening after we had migrated from SQL2k to 2k5 SP3. It happened a number of times in the first week on the new server and then was running fine for a couple of months and has now started happening again regularly.
The error generated is:
Executed as user: EBS\SQLAgent. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot open the Microsoft Jet engine workgroup information file.". [SQLSTATE 01000] (Error 7412). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
Fortunately the resolution is simple: restart the SQLSERVER service but obviously we would like to get a more permanent solution where we could stop these errors happening at all.
Has anyone encountered this issue before and know of a resolution? Could anyone suggest what I might do to further investigate the problem?
Thanks in advance for any replies.
August 5, 2009 at 5:28 am
Anyone come up with any ideas on this one? I'm having to restart the SQL service 3/4 times a day at the moment to get the jobs running again.
I have also found that the linked servers to the access database also fail at the same time as the openrowsets.
I'm totally stuck for ideas to try now.
August 6, 2009 at 6:34 am
I know you said "don't ask" with respect to Access 95, but seriously, you DO need to consider at least the possibility of at least moving to Access '97, where the changes from Access 95 are at least a lot less likely to cause problems within Access, as opposed to trying to move up to Access 2000, 2003, or 2007 formats. Also, it's complaining about the workgroup file, and if anyone is using the database at the time, it may be that once SQL Server sees it as in use, it for some reason never changes it's opinion? Hard to tell.
Obviously, re-starting SQL Server even once a day is way too much trouble. Also, here's another thought: Has anyone considered migrating the data in these databases up to SQL Server? Microsoft has a free download called SQL Server Migration Assistant, but I'm pretty sure the lowest version of Access that can be dealt with is beyond Access 95.
You desperately need to get away from Access 95 - it's got any of a number of problems that don't go away until at least Access 97 or later, and this may just be one of them.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 6, 2009 at 6:44 am
I do take all the points about access 95 - it's one of those legacy systems that has been allowed to live on long, long past its sell-by date. There is a replacement for it currently being written in .NET so moves are well underway to rectify the situation, we just have the problem of supporting it in the interim. A whole world of bountiful joys that falls squarely at my feet. Trust me, I hate access with every sinew in my body, I long for the day when I no longer have to support access!
Your response did give me an idea though, I could try setting a different workgroup file. No idea if that would work or not but it's worth a punt.
August 6, 2009 at 12:11 pm
I hear ya... supporting legacy mistakes is always a PITA, but somehow, it always has to get done. I've never hated Access though... it's a darn good tool when used appropriately. Such a shame that it's so often abused and maligned, when the problem isn't always Access, but the way it's being used. Good luck with your attempt on the WIF.
Come to think of it, maybe there's another way to solve the problem. How about a linked table within an Access 2003 or even Access 2000 database, that points to the actual table within Access 95? How about a VBScript that could be scheduled that would import tables out of the '95 version and into 2003 or 2007 versions of Access, and then the SQL inputs could go against those intermediate databases? As you referred to in your post, this might be "worth a punt"...
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply