topic Re: Receiving a file already in use where file resides in SAS Programming

Receiving a file already in use where file resides

KAS25 — Tue, 04 Jan 2022 00:06:27 GMT

Good evening I am trying to resolve an issue with export to a file which is an access database file. They execute the SAS script that updates this accdb file on the :drive but the user is receiving file already in use. I tried to use a backup of the file but getting the same results. Also trying to help the guys who made some security changes to the server figure out if it's something that locked the file. Any thoughts or assistance would be greatly appreciated. when I click on the file the Access database opens fine. Seem like we just can't update it/export to it. Error below:

NOTE: There were 101 observations read from the data set ABCOSPPS.SADJ_DDCT.

NOTE: The data set ABCOSPPS.SADJ_DDCT_BASIC has 101 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.10 seconds

user cpu time 0.01 seconds

system cpu time 0.04 seconds

memory 662.28k

OS Memory 15760.00k

Timestamp 12/31/2021 11:12:16 AM

Step Count 7 Switch Count 0

NOTE: %INCLUDE (level 1) ending.

1468 PROC EXPORT DATA= ABCOSPPS.Sadjustment

1469 OUTTABLE= "Adjustment"

1470 DBMS=ACCESS REPLACE;

1471 DATABASE="S:\sas\\access\ABCOSPPS_Current.accdb";

1472 RUN;

ERROR: Connect: Could not use 'S:\sas\\access\ABCOSPPS_Current.accdb'; file

already in use.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 1.40 seconds

user cpu time 0.09 seconds

system cpu time 0.21 seconds

memory 1055.15k

OS Memory 16016.00k

Timestamp 12/31/2021 11:13:23 AM

Step Count 8 Switch Count 0

Re: Receiving a file already in use where file resides

EyalGonen — Tue, 04 Jan 2022 12:00:55 GMT

This message typically means someone or some process it using this file in parallel to SAS and is locking it and so SAS cannot update this file till this other user/process releases the lock.

You or your IT can find who is locking this file by using 3rd party tools like "Process Explorer" from Sysinternals: https://www.mysysadmintips.com/windows/clients/593-find-what-is-locking-a-file-using-sysinternals-process-explorer

Re: Receiving a file already in use where file resides

andreas_lds — Tue, 04 Jan 2022 12:24:22 GMT

Does the library ABCOSPPS point to S:\sas\\access\ABCOSPPS_Current.accdb? Proc export needs exclusive access to the access-file.

Re: Receiving a file already in use where file resides

KAS25 — Tue, 04 Jan 2022 13:42:27 GMT

YES I believe everything is in order with that. We just can't find anything that has that file in use already.

Re: Receiving a file already in use where file resides

EyalGonen — Wed, 05 Jan 2022 09:32:29 GMT

Have you seen what I suggested? Try 3rd party tools like "Process Explorer" from Sysinternals to find out who is locking this accdb file: https://www.mysysadmintips.com/windows/clients/593-find-what-is-locking-a-file-using-sysinternals-pr...

Re: Receiving a file already in use where file resides

a_SAS_sin — Wed, 05 Jan 2022 11:12:00 GMT

In our SAS environment we also started getting the same error with code that worked perfectly fine before.

The only SAS updates we have done recently was to apply the changes to fix the Log4j Vulnerabilities as recommended here: SAS Help Center: Introduction

The weird thing is that other MS access databases still work. I am not sure if that is related with the fact that on our local machines we have Office 365 and on the SAS server we have Office 2016?

We will contact IT Support and if we find a solution we will let you know.

Best Regards

Re: Receiving a file already in use where file resides

Michelle — Wed, 05 Jan 2022 18:26:27 GMT

Also started having this issue at my organization this week.

Re: Receiving a file already in use where file resides

KAS25 — Wed, 05 Jan 2022 18:44:01 GMT

Thanks for the reply and Yes please let us know if you find out anything. We still trying to resolve. This is our only SAS that updates MS ACCESS DB.

Re: Receiving a file already in use where file resides

cb4jc4 — Tue, 11 Jan 2022 03:36:16 GMT

Yes please share any findings - Im having a similar issue with a SAS script that has been running a few years that updates a single table in an .accdb 😞

Re: Receiving a file already in use where file resides

andreas_lds — Tue, 11 Jan 2022 06:25:31 GMT

@KAS25 wrote:

YES I believe everything is in order with that. We just can't find anything that has that file in use already.

Sorry for the late reply.

Is it possible to open the access-file after all sas processes are stopped?

If yes, then, as i have mentioned, proc export needs exclusive access. This means that you can't use proc export to write to the same file you have opened via libname statement. Btw: why not use proc copy to write the dataset to the access-file? I have hardly used access in the last 15 years, so i am not sure this will work.

Windows server admins don't need the process explorer from sysinternals, the os has a tool showing all open files.

Re: Receiving a file already in use where file resides

a_SAS_sin — Tue, 11 Jan 2022 22:15:22 GMT

Just an update, we have not yet fixed the error but we found a workaround. In our organisation, we noticed that the issue was only happening when accessing MS access databases on network locations, hence the workaround was to copy the files to the server and updated the code to use this location. This solution is, nevertheless, temporary thus we are still investigating and because it is happening with many of you it is making us doubt if the issue is just related with our infrastructure or with some more generic SAS or Microsoft update?

Re: Receiving a file already in use where file resides

Michelle — Wed, 12 Jan 2022 13:14:05 GMT

I also wonder if it's a broader issue with SAS and/or Microsoft. SAS Admin at my org contacted SAS since it's not just our org, and waiting on response.

Re: Receiving a file already in use where file resides

JuanGSR — Thu, 13 Jan 2022 14:33:57 GMT

We are also experiencing the same problem - working on some machines but not on others.

Re: Receiving a file already in use where file resides

KAS25 — Thu, 13 Jan 2022 16:47:27 GMT

Thanks all. No we still haven't found a resolution yet. We have a temporary workaround the seems to satisfy the user for now. SAS support hasn't provided us with anything of significance yet.

Re: Receiving a file already in use where file resides

cb4jc4 — Fri, 14 Jan 2022 19:20:54 GMT

Thanks for the update! I actually noticed the file location factor - I’ve
been swamped and haven’t tested the workaround ye but realized .accdb files
on a shared g drive versus the network share did not have the “file in use”
error message. I’ll hopefully find some time to further explore this
evening but solid workaround if it works. The core issue for us seems to
have started 12/31/2021 which has me confused on what could be the problem
but I wonder the same thing. I’ve reached out to our network team and will
hopefully have more information soon.

Re: Receiving a file already in use where file resides

JuanGSR — Thu, 20 Jan 2022 15:05:13 GMT

we have encountered more machines that are having the same problem after loading the log4j fix package and also microsoft updates. may be a coincidence but I am not sure.

Re: Receiving a file already in use where file resides

Michelle — Tue, 01 Feb 2022 13:40:39 GMT

Has anyone received any updates from SAS yet? Curious if they have a fix in the works.

Re: Receiving a file already in use where file resides

Michelle — Tue, 22 Feb 2022 16:12:45 GMT

Update in case anyone else is still struggling with this - at my organization it turned out to be a need to apply updates to Microsoft Office. Once all the Microsoft updates were done, SAS could once again export to Access without the file already in use problem.