Failed to open file in write mode for file - QS Server 2.0.6.0

[marksouzacosta]

I'm getting an intermittent problem during an incremental load.

Basically I load my new and updated records from a Database and concatenate it with the Historical records stored in a QVD file.

The problem happens when I store the updated set of records in the previous loaded historical QVD - this is pretty much the standard Incremental Load Process.

So I get this error:

"Failed to open file in write mode for file"

Again, this is an intermittent problem and I'm not able to reproduce it.

1 - Is there anyone else having this issue?

2 - Is there a way to avoid this problem?

I'm thinking this may be a Qlik Sense bug.

Thank you in advance,

Mark Costa

[marksouzacosta]

I'm sorry to hear that Xinzhen. That solved our problems 100% of the time, even in different implementations.

Question:
Are you loading the QVD that you are trying to overwrite with the STORE command in the same load script - like in an incremental load? If possible, please share your load script.

Regards,

Mark Costa

[#3]

Hi Mark,

Yes we need to implement incremental load on multiple datasets and the error always occurs on the largest qvd size.

This is the step of incremental load.

(incremental load and concatenate other datasets)

1. load new records from database into SignInIncr table

2. //This step is not necessary but I was trying to debug the problem, the qvd got updated correctly

    store SignInIncr into [$(filepath)/SignInIncr.qvd] (qvd);

    Call Qvc.WaitForFileToClose('($(filepath)/SignInIncr.qvd'); //made your code a sub function to call

     drop table SignInIncr;

3. load all record in SigninIncr.qvd and concatenate with history data in Signin.qvd into SignIn table

4. //This step is where the intermittent error happens

   store SignIn into [$(filepath)SignIn.qvd] (qvd);

   Call Qvc.WaitForFileToClose('$(filepath)SignIn.qvd');

   drop table SignIn;

Here is the error message I got:

2016-12-14 04:02:14 0290 store SignIn into [lib://..filepath.../SignIn.qvd] (qvd)

2016-12-14 04:02:14      Failed to open file in write mode for file \filepath\SignIn.qvd

2016-12-14 04:02:14      Error: Failed to open file in write mode for file \filepath\SignIn.qvd

2016-12-14 04:02:14      General Script Error

2016-12-14 04:02:14      Execution Failed

2016-12-14 04:02:14      Execution finished.

I called the Sub procedure after every "store" in this load script. It seems that the execution didn't get to the "buffer" code section when the error happens?

Did I miss anything?

Xinzhen

[marksouzacosta]

Bingo!

Ok, so this is where you'll need another workaround, yes, another Qlik Sense bug:

In the same load script, you cannot load/store the same QVD file. Like you noticed by yourself, sometimes we got an error doing that.

What I do to avoid this problem in my incremental loads is to rename - you can make a copy too - of the historical QVD file. You should not load at any point of your load script the original historical QVD file, just the renamed or historical one. Your load script should look like that:

//*************************************************

// 1. Rename or duplicate your historical QVD file - if it exists already (I will not cover this treatment in this example)

EXECUTE cmd.exe /c REN "\\filepath\qvdname.qvd" "TEMP_qvdname.qvd";

// Note 1: I just have renamed my QVDs because it takes time to copy large QVD files.

// Note 2: You have to allow your Qlik Sense Server to run EXECUTE commands. You can Google for that

// Now you can continue with your regular incremental load:

// 2. Load new/updated/deleted records from Database;

// 3. Concatenate the new/updated records with the renamed QVD file. Just load the data from your TEMP QVD file, never at any point in your load script load the data from the original historical QVD file;

// 4. Store the QVD file using the original name of the QVD file. Since your original QVD file was not loaded you will have no problem on this Store command;

// 5. Do the workaround to wait the Store command to be ready; and

// 6. Delete the temporary QVD file - not required, but will save some space

EXECUTE cmd.exe /c del /f /q "\\filepath\TEMP_qvdname.qvd";

//*************************************************

That is it. You should be good with those two workarounds.

Please let us know if that worked for you.

Regards,

Mark Costa

[#5]

Hi Mark,

Thanks for the diagnosis! The task ran successfully today.

Instead of modifying settings.ini to allow EXECUTE, I have made a copy of the qvd file to be a placeholder.

// Make a duplicate copy of qvdname.qvd into temp_qvdname.qvd (one time)

// 1. Load new/updated records from database;

// 2. Concatenate the new/updated records with data from temp_qvdname.qvd

// 3. Store the data table into qvdname.qvd

// 4. Do the workaround to wait for Store command to be ready

// 5. Load all data form qvdname.qvd and Store it into temp_qvdname.qvd (for next day execution)

// 6. Do the workaround to wait for Store command to be ready

This sequence seems to work even though I did Store and Load from the same qvd file.

Thank you again for your help!

Xinzhen