Not often, but meanwhile 2 or 3 times I get this error (with different
filenames off course):

" ElevateDB Error #600 An error occurred with the statement at line 35
and column 28 (File manager error (Access denied to the file
C:\Beckersoft\Stores\Informa_EDV1_In\EDV1-2016-05-10 16-18-56.157.EDBUpd)) "

The error occurs after the update file has completely been loaded and
should be deleted.

The procedure is running every 5 minutes and usually everything works
fine, just these few errors occur.
Since the update file is not deleted (but has been already loaded), I
get errors for each insert statement (duplicate primary key...) when the
application tries to load the updates from that file one more time.

Any idea what can be the reason for the update file not beeing deleted?
And what would be the best way to handle this problem?

Thanks Uli

Uli,

<< Any idea what can be the reason for the update file not beeing deleted? >>

My first guess would be AV software.

How often are the update files being created using SAVE UPDATES ?

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> My first guess would be AV software.

No AV software active on that server.

> How often are the update files being created using SAVE UPDATES ?

I gave you a wrong information: "every 5 minutes" refers to a different
procedure. Sorry about that.

Loading the updates happens on a remote server. On a local PC 2
procedures are executed:

         SendUpdatesSP.ExecProc;
         RemoteLoadUpdatesSP.ExecProc;

The local PC creates the update file, copies it to the remote server
(SendUpdatesSP). After that a procedure on the remote server is executed
to load these updates (RemoteLoadUpdatesSP). Within this procedure there
is a "delete" statement after the update-file has been loaded.

Thanks Uli

Uli,

<< Loading the updates happens on a remote server. On a local PC 2 procedures are executed:

         SendUpdatesSP.ExecProc;
         RemoteLoadUpdatesSP.ExecProc;

The local PC creates the update file, copies it to the remote server (SendUpdatesSP). After that a procedure on the remote server is executed to load these updates (RemoteLoadUpdatesSP). Within this procedure there is a "delete" statement after the update-file has been loaded. >>

Hmm, this is a weird one.  My next suggestion would be to use something like Process Monitor to log the activity on the remote file system and see what process has the file open:

https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx

Tim Young
Elevate Software
www.elevatesoft.com

Uli,

Do you have anything else on the remote side that might be opening the file occasionally, such as something that queries the store files system information table ?

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Do you have anything else on the remote side that might be opening the file occasionally, such as something that queries the store files system information table ?

I just doublechecked that: no, absolutely nothing.

Since that seems difficult to find out, I plan to use this workaround:

Create an exception handler for the delete command and write the
filename into a table if deleting fails. Then this file can be excluded
/ deleted before the next loading of updates.

Uli

Uli,

In my replication systems I first rename a loaded Updates file: ***.EDBUpd  to ***.OLDUpd and then delete later (actually about 1 week later).

Using this method I do not see your problem and "OLD" files are not re-loaded on the next run of the replication. It is safe and easy. I think that there are situations where it is legal to rename a file, but not to delete it when it is in use by other processes.

I would suggest trying altering your procedure to RENAME FILE rather than DELETE FILE & see whether this resolves the problem.

--

Also, keeping the OLD files means they act as a form of audit trail, as the CREATE MEMORY TABLE "<name>" FROM UPDATES statement can be used to generate a (memory) table which can be queried to review the changes made to the database in the period. This can be useful, particularly when multiple changes have been made to particular records as you are trying to figure out who/what/why/when.

Adam,

> I would suggest trying altering your procedure to RENAME FILE rather than DELETE FILE & see whether this resolves the problem.

Thanks for that. The problem is that it's not clear why the access to
the file is denied. At least I don't see any reason for that. And I
doubt that renaming of the file is possible if it's really in use.
Interesting idea though to keep the files for a while and preventing
reloading by renaming!

Uli

Uli Becker wrote:
<<
Since that seems difficult to find out, I plan to use this workaround:

Create an exception handler for the delete command and write the
filename into a table if deleting fails. Then this file can be excluded
/ deleted before the next loading of updates.

>>

Uli,

Tim mentioned sysinternals process explorer already but another utility that sysinternals have is handle (https://technet.microsoft.com/en-us/sysinternals/handle).

It's a command line utility so in theory you could execute it whenever deleting of the file fails and dump all process handles into a log file which you can later review to see which ones had that file open.

Just another suggestion.

Raul

Raul,

> It's a command line utility so in theory you could execute it whenever deleting of the file fails and dump all process handles into a log file which you can later review to see which ones had that file open.

Very good hint! Thank you.

Uli