topic Re: No application is associated with the specified file for this operation in SAS Programming

No application is associated with the specified file for this operation

seandavis69 — Tue, 21 Nov 2017 19:28:07 GMT

We are running SAS 9.3 on a Windows Server;

We have an ongoing issue with the error ;

No application is associated with the specified file for this operation.

We see it intermittently but with some interesting factors;

1. The error is intermittent, it does occur within the first 5 minutes or so of an hour nearly exclusively, though we have

also seen it occur up to five minutes after the half hour too, say 8:35.

2. It only happens when we perform an Oracle bulk load via a proc append function which transfers control to SQLLDR.exe and creates a dat, log and ctl file, the error occurs prior to actually beginning the load.

3. Given the two conditions above if we were to attempt a PROC append and use the bulk loader options which invoke SQLLDR, and it was 5 minutes after the hour, we would likely encounter a "No application is associated with the specified operation" error. As a side note, these are the only conditions we have ever seen this error, also we run many bulk loads via proc append that do not fail, because they do not run during those particular timeframes.

We have been to SAS support numerous times throughout years, yet this issue persists, unresolved, I would be curious to know if anyone else has encountered this or a similar issue or solution?

No application is associated with the specified file for this operation.

ERROR: Command could not be executed.

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE APPEND used (Total process time):

real time 0.74 seconds

user cpu time 0.32 seconds

system cpu time 0.21 seconds

memory 381.93k

OS Memory 12588.00k

Timestamp 11/20/2017 06:08:07 PM

Re: No application is associated with the specified file for this operation

ChrisNZ — Mon, 27 Nov 2017 00:54:46 GMT

What is the command that fails? Show it to us.

Re: No application is associated with the specified file for this operation

seandavis69 — Tue, 28 Nov 2017 14:03:03 GMT

Example:

libname test oracle user=xxxxx password=xxxxx path='xxxx' schema=xxxx;

proc append base = test.dummy_data(BULKLOAD=YES

bl_direct_path=yes

bl_delete_datafile=yes

bl_return_warnings_as_errors=yes

bl_datafile="c:\test\test.dat"

bl_control="c:\test\test.ctl"

bl_badfile="c:\test\test.bad"

bl_log="c:\test\test.log")

data = sashelp.air;

run;

Re: No application is associated with the specified file for this operation

seandavis69 — Fri, 29 Dec 2017 11:27:12 GMT

We have an UPDATE to this issue I wanted to share. We found a LIBNAME override that was inconsistent between our environments. One of our test environments encountered this issue almost never. The LIBNAME was in FCSAUTOEXEC.SAS (part of the SASAML startup for assigning libraries, formats, etc.)

/* Assign default FORMATS library - FCF auto overwrites with 'fmts' reference when run. */

libname FORMATS 'F:\Projects\FCS\formats' filelockwait=30;

options insert=(fmtsearch=(FORMATS.formats));

The parameter filelockwait=30 seems to be the root of our issues, we first attempted to change it to a much higher value, 120, however, this caused a new issue, we began to see issues were indexes on table loads were becoming unusable. The frequency was consistant on certain table loads.

ERROR: ORACLE execute error: ORA-01502: index 'FCFKC.XPKFSK_TRANSACTION_AL' or partition of such index is in unusable state.

So we changed the value to filelockwait=3, which is how it was set on one of our test systems that had the least issues. This was done on Nov. 21st on one system and Dec. 8th on another. We have not encountered the issue I reported with "no application is associated" on any job anytime since that change was made. I'd like to know more about why this "tunable" parameter can cause such havoc and what behind the scene is going on, what things are in conflict and more about WHY this change is helping. Anyone with any insight to adjustments to filelockwait (sans google search) would be appreciated. Hope this helps someone out there, this has plagued us for years.

Re: No application is associated with the specified file for this operation

seandavis69 — Tue, 10 Apr 2018 18:13:56 GMT

Follow up 4/10/2018 - The fix described above was NOT the ultimate solution to this issue. It did impact things in a subtle enough way to avoid this issue for a period of time (just due to the timing of things). We believe we have now solved this issue with a change to Group Policy. All of out SAS programs are executed via batch files under control of "Maestro" by Tivoli a job scheduler. We only encounter this error when a program is running under job scheduler execution, many dozens if not a hundred jobs are run daily on multiple SAS systems. The error specifically occurs when a SAS.exe process has started say at 1:04AM, then a Windows Event 1530 occurs "Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards." after the SAS job say at 1:06AM, and then if during your job execution you call the Oracle Bulkloader.exe prior to the end of the SAS.exe you would get the "No Application is Associated..." error and the proc append would fail. The events would have to occur in this order for this to happen.

To resolve this we deployed the same solution as was described in this article - https://support.microsoft.com/en-us/help/2287297/a-com-application-may-stop-working-on-windows-server-2008-when-a-user

Short version - in GPEDIT.msc enable -"Do not forcefully unload the user registry at user logoff" policy setting for users

Note The same issue can occur on Windows Vista, Windows 7, Windows 2008, and Windows 2008 R2 and Later OS version as it is now default behavior to force user registries even while still logged on. You can verify this is happening to you by noting the time your job began and the cooresponding "warning" of 1530 in the event viewer just after your job began but prior to it's transfer to another program like Oracle bulkloader.exe. Hope this helps someone out there with a similar issue. If you have seen this please respond, it has plagued us for years as a "intermittent anomaly". Also, just FYI, you may need to ensure that if you make this GP change that your network policy will not override the local policy on the server.

Re: No application is associated with the specified file for this operation

ChrisNZ — Tue, 10 Apr 2018 22:54:48 GMT

@seandavis69 Well done!
Remarkable detective work, and thanks for posting the outcome. I am impressed.

The joys of Windows' policy and registry settings are with us forever.