Link to home
Start Free TrialLog in
Avatar of MaxwellTurner
MaxwellTurnerFlag for Canada

asked on

Compacting dbase via unattended scheduled task - DELAYED WRITE FAILED

I will explain what I am doing, then the error I keep recieiving.

I run a series of ACCESS 2000 macros at night and have scheduled them to run using a batch file.  The batch file uses the names of tasks that have been set up in windows task scheduler.

MY BATCH FILE:

::--Start script.cmd---
@echo off
setlocal
::Put your task names in order of execution here.
::Surround names with qoutes. Use space as delimiter.
set tasklist="ImportLVTCData" "Compact Sales" "Import Web Data" "Compact Webdata" "Import Website" "Compact Website" "Compact LVTC1_DATA"


::Main routine
for %%c in (%tasklist%) do (
schtasks /Run /TN %%c
call :loop %%c
)
endlocal
goto :eof


::Task status checker
:loop
:: Pause. If you have sleep.exe
:: or other pause utility, put it
:: here instead of ping command
ping -n 5 127.0.0.1 > nul 2>&1

for /f "tokens=1,3,4 delims=," %%i in ('schtasks /QUERY /FO CSV /NH') do (
    If %%i==%1 (
        set var1=%%~j
        set var2=%%~k))
If "%var1:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
If "%var2:~0,-1%"=="Runnin" (
    echo Task %1 is running. Waiting...
    goto loop)
echo Task %1 ended.
goto :eof

::--End script.cmd---

Sometimes it works with no problems, but more often than not, I am getting the following error during the task IMPORTLVTCDATA.  

DELAYED WRITE FAILED:
Unable to save all the data to db1.mdb.  All the data will be lost.  May be caused by a failure of hardware or network connection.

. . . when I try to open the dbase it tells me that I cannot open it because it is currently in use by someone else - even if I reboot - and it IS NOT being used by anyone else!!!

This particular task imports 8 *.dbf (dbase4) tables then compacts on close.  I believe that the error is occuring during the compact - this task used to ONLY import the tables followed by another task that would compact the dbase(via the /compact switch) & I used to always get that error during the compact task - now that I compact on close, the error seems to be happening after the importing of the tables.

ACCESS has added a table called: MSysCompactError - it has the following columns:

ErrorCode      ErrorDescription      ErrorRecid      ErrorTable
-1611      Could not find field 'Description'.            INVENTORY
-1611      Could not find field 'Description'.            MSysObjects

. . . under the 3rd column, it lists a row for each table & query in the dbase plus 6 rows with the "MSysObjects"????

The dbase that I am trying to compact is on a network drive (not local), but I am sure that nobody is using it at the time it is being compacted.  I am using XP pro - I recently switched from Win 98 & never had this problem.

Any thoughts on this would be greatly appreciated.

MAx
Avatar of jadedata
jadedata
Flag of United States of America image

Greetings MaxwellTurner!
  I think this is where you might want to post a 20pt question in the Windows OS TA's to get one of those crew in here.  This reaks to me of a WinXP deal, with a touch of disk write commits interferring in the compact operation.

  Are you specifying Exclusive DB use during these operations?

 Have you considered getting a copy of FMS Inc's  Total Access Agent??  (www.fmsinc.com)  This REALLLLY saves a buttload of effort managing and maintaining Dbs in a shared production environment.  (and it can back up data files WHILE there are users on line!!!!)

regards
jack
Avatar of MaxwellTurner

ASKER

jadedata,

I typed this in the DOS section, then cut & pasted it into here , then into XP then back here - I couldn't decide where the best place was to place it but I thought that the error probably was an ACCESS related error so I ended up here.

Good idea to post alink elsewhere though - I will do that.

Oh yeah, I should probably note that none of my tables, queries, or macros have or mention a field called "description"!

Max
Hi MaxwellTurner,

>>DELAYED WRITE FAILED<<
This normally indicates that your drive is attached with a 40-wire IDE cable instead of the 80-wire IDE cable which is used nowadays.
I suggest you to check this and replace it if neccesery.

Greetings,

LucF
SOLUTION
Avatar of Pete Long
Pete Long
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Some further reading:

"Delayed Write Failed" Error Message When You Manage Files in Windows XP
http://support.microsoft.com/?kbid=330174

Hmm, sorry Pete.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jadedata - EVERYONE GETS POINTS IF I CAN RESOLVE THIS!!!

QUESTION:  The database that I am trying to compact is on a directory on my companies server, not on my local machine.  I assume that the 80 wire cable that I would want to check is on the server not my local machine - not sure though because I think the db1.mdb is trying to be written to my "My Documents" folder.  Also, if I am using a 40 wire cable, does that means that the hard drive is also 40 wire and will have to be upgraded - I have a brand new IBM workstation, so I doubt that it is 40 wire on my machine?  My "Enable write caching on the disk" is turned on.

Max
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What would be the issues surrounding bringing the data file to be compacted over to the local drive for processing, and then transporting and overwriting at the server after the operation certified correct completion?
Okay,

The dbase that I am compacting is on F: - our company server - but the error message indicated that the db1.mdb was being written to "C:\Documents and Settings\Maxwell Turner\My Documents" - on my local machine.  

I assume that I should be checking my local machine because that is where the write error is occuring - is that correct?

I just checked & my local machine does have an 80 wire cable - I physically counted 20 & it was about 1/4 the way across so its definitely not 40 pin.

Also, my "Enable write caching on the disk" is ENABLED.  What are the consequenses of turning this off?  Will it really hurt my computers performance?

LucF:

Your link described my probelm exactly - it is for Win 2000 though, I noticed.  Do you figure it will work for XP as well?

Maxwell
Could be that your version of the page is different than mine (I get a dutch version) and it clearly explains it's for both win2k and winXP to me. But as I've seen several times it could be different, let me know and I'll try to find another one for you.
This is making more sense in light of the way Access will write a temporary copy of a database to a location pending successful completion of the compact.  I don't think this would change even if you brought the file over to the local drive for processing,...
now that I think of it...
LucF,

Sorry - you were right - I didn't look hard enough.  Sounds like I will have to contact Microsoft for the fix.

BTW - I attempted to change my registry as the workaround describes, but it was already the way it was supposed to be.

Max
This is just an assumption, but it could be this problem will be fixed in SP2 for winXP, you might want to check if it's allready available for you.
I have called IBM, who in turn told me to call Microsoft & they actually emailed me the HOT FIX described in the article that LucF recommended (http://support.microsoft.com/?kbid=321733) - Its a zip with 2 Application files - I don't know which one to open so I am currently on-hold with MS for installation instructions.

I am pretty sure that this will work - I will keep you posted.

Max
Ok, good luck ;-)
Still on hold . . . . . . .

Max
I recieved and applied the HOT FIX so I guess when my tasks run tonite, I will know if it was fixed.

Thanks everybody for such prompt replies!!

Max
Glad to help and let us know the outcome.

(you'll have to delete your pointer in the OS-TA now as it has no use anymore)

Take care,

LucF
Well,

I didn't work - same error on my machine this morning.

My local machine has a 80 wire cable, but the "Enable write caching on the disk" is ENABLED - I am abit leary to disable this - I don't really want to sacrifice performance even though I have a pretty quick computer (P4 2.8).  I am going to call Microsoft again to see if they have any other ideas before I resort to that.

I have the option also of moving my macros off to another Win 98 box which previously never had these problems.

Max

Bummer...

In the second link I gave, check the servers registry for that setting, try the workaround there. (maybe you didn't read carefully enough and missed that the workaround had to be performed on the server)
I have a quick question:

When it is said:

"Ensure that the UDMA hard disk controller is using an 80-wire"

this is refering to the actual cable used to connect the hard drive? Correct?

Max
Yep, old IDE cables had 40 pins and 40 wires, newer cables have 40 pins and 80 wires where every second wire is only used for electro magnetic shielding because of the high frequencies of signals used through the cable.
Just as a lark,... try setting the Access ODBC time out default higher to allow more time to be able to retrieve data over the network.  I will state that there is no reason this should work, but I would try it were I in your shoes.

or,...
  I use this process for compact/repair/backups
(pseuda
  copy productiondatafile to tempdatafile  (in your case to local drive)
  rename productiondatafile to bakdatafile(appnameyymmddhhnn.mdbbak)
  compact tempdatafile to newproddatafile
  validate bakdatafile (open it and look around a bit)
  validate newproddatafile (open it and look around a bit)
  kill tempdatafile
 

I believe that this is fight between XP wanting to cache to disk immediately and having to wait for data from the network over the lan
am I missing some piece?

Good point jadedata ;-)

If I'm right, disabling "Enable write caching on the disk" would stop this fight at the cost of some performance.
If yours works it's the best way I guess.

Maybe we make a great team together ;-)

LucF
LucF,

You are indeed correct - I just ran all my tasks 3x and not a single errer after I disabled "write caching on the disk".  Did not notice any decrease in performance either - if there is any it is very minimal.  Perhaps my computer is fast enuf that I just don't notice it.

Anyways, thanks guys (I am assuming yer guys - take no offence if yer not)!  Its been an adventure!

Max
You're very welcome Max ;-)

Take care,

LucF
jadedata

"Hardware" Geek!!!!!  Im a network Engineer :p
Pete:  Ok a Web Geek!
Im aghast I come through to re-read my old comments and find a slur on my otherwise spotless character im so upset Im going to bed g'nite Jack ;)
Your reputation is only appears spotless because it is so evenly covered in blemishes...
nitey nite!
ROFL - you appear to know me too well, there aint no blemishes however on my boyish good looks ;)
http://petenetlive.hopto.org/about.htm thats not blemishes, thats a windswept and interesting aura!
OH COOL!!! I love the Stinkclaire!!!!  I have to get a picture of my Tandy MC10 with a killer 4K of RAM!!  oooo and let's not forget that mind-blowing 16K ram expander!!  Love those little chicklet keys!!
And I tought I had some old computers still running :(
I have both and MSX-1&2 the last one with a whopping 128kb of video memmory and a Single Side Double Density diskdrive (3.5")
Thats most strange, my Inbox shows a post by ee_ai_construct wonder how that could have happened on a question thats been quiet for over a month? "pete ponders smirkily"