topic Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET in Administration and Deployment

Admin Coaches Corner set for March 8, 2:00-3:00pm ET

ShelleySessoms — Mon, 20 Feb 2017 17:34:17 GMT

Do you have a burning administration or deployment question? Need help figuring out what that error message means? Have an installation conundrum? Have a metadata, server or directory question you would ask at SAS Global Forum, if only you were attending? Well, now is your time!

SAS employees from R&D, product management, technical support and customer loyalty will be on hand to answer your questions in near real-time. Just sign in to communities.sas.com, head to the Administration community and post your question. We will be standing by to provide helpful advice, ask follow on questions, or direct you to the appropriate resources.

Remember: it’s one hour only, 2:00-3:00pm ET on March 8, for the near real-time response; but, as you know there’s always someone here to help.

We’ll see you then!

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 01 Mar 2017 23:25:37 GMT

Hello,

I have a pre-question for this event:

This code works great in SAS EG but when placed on the server, it kicks back this error message (in red):

%include '/sas/shared/628562/pass.sas';

libname DVM oracle user=&user_a password=&pass_a path=DVMTFPRD schema=APP_ALL;

data _null_;

date1=today();

call symput ('paydate',put(intnx('day',date1,-2,'end'),date11.));

call symput ('busdate',put(intnx('day',date1,-1,'end'),date11.));

run;

%put &busdate;

%put &paydate;

proc sql;

connect to oracle as oracle (user=&user_a password=&pass_a path=DVMTFPRD);

create table aggregate1 as select distinct * from Connection To oracle

(select

a.Business_dt,

a.Next_Pmt_Dt,

a.sale_dt,

a.service_entity_num,

a.service_entity_nm,

a.contract_num,

a.member_num,

a.fac_cd,

a.suppression_cd,

a.Loan_and_Credit_Life_Bal_Amt

from vh_coll_contract_hist a inner join

dm_d_calendar b

on a.report_category_nm = b.report_category_nm

and a.business_dt = b.business_dt

where b.region_loaded_cd in ('NA','ALL')

and a.report_category_nm in ('DAILY', 'SEMI-MONTHLY')

and a.Next_Pmt_Dt between '15-jan-2017' and %bquote('&paydate.') /** We started the project on Jan 15 **/

and trunc(a.business_dt) = %bquote('&busdate.')/** business date is yesterday **/

and a.service_entity_nm in ('FAIRFIELD','TRENDWEST')

and a.delete_crmast_ind = 'N'

and a.suppression_cd in ('7','%','A','C','H','M','N','O','S')

and a.fac_cd = 'Q'

and a.loan_and_credit_life_bal_amt >0

and a.Contract_Status_Cd = '0'

);

Disconnect from oracle;

quit;

data aggregate2;

set aggregate1;

dayslate = intck('day',datepart(next_pmt_dt),datepart(business_dt));

run;

proc sort data=aggregate2;by dayslate;run;

proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'

dbms=xlsx replace ;

sheet = "&busdate.";

run;

filename mymail email ("adminemail@abc.com")

subject="Pool Code Q Project Daily file"

attach=("/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx" content_type="application/xlsx");

data _null_;

file mymail;

put 'All,';

put "Here is the daily file as of &busdate. for the Pool Code Q project. Please let me know if you have any questions on the data.";

put;

put 'Thank you,';

put 'Max';

run;

filename mymail email ("firstname.lastname@abc.com" "firstname2.lastname2@abc.com" "firstname3.lastname3@abc.com"

"firstname4.lastname4@abc.com" "firstname5.lastname5@abc.com" "firstname6.lastname6@abc.com" )

subject="Pool Code Q Project Daily file"

attach=("/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx" content_type="application/xlsx");

data _null_;

file mymail;

put 'All,';

put "Here is the daily file as of &busdate. for the Pool Code Q project. Please let me know if you have any questions on the data.";

put;

put 'Thank you,';

put 'Max';

run;

62 proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'

63 dbms=xlsx replace ;

64 sheet = "&busdate.";

65 run;

Error creating temporary file for XLSX file -> /sas/shared/mfritz/SAS/Data/pool_code_Q//Pool_Code_Q.$$1 . It is either not an

Excel spreadsheet or it is damaged. Error code=8000101D

Requested Output File is Invalid

ERROR: Export unsuccessful. See SAS Log for details.

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

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.21 seconds

cpu time 0.01 seconds

Any thoughts?

Thanks,

Sharda

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

MichelleHomes — Wed, 01 Mar 2017 23:29:19 GMT

FYI, I have edited your post to remove the email addresses.

When you say the code is "placed on the server" do you mean it is run by a SAS service account and not by your identity? It could be permissions related for how it is being executed by the service account not having access.

Kind Regards,

Michelle

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:13:27 GMT

I have two scheduled Jobs on the Grid kicking back this error message based on the code snippets below:

61
62 proc export data=aggregate2 outfile='/sas/shared/mfritz/SAS/Data/pool_code_Q/Pool_Code_Q.xlsx'
63 dbms=xlsx replace ;
64 sheet = "&busdate.";
65 run;

Error creating temporary file for XLSX file -> /sas/shared/mfritz/SAS/Data/pool_code_Q//Pool_Code_Q.$$1 . It is either not an
Excel spreadsheet or it is damaged. Error code=8000101D
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.21 seconds
cpu time 0.01 seconds

-----

542 proc export data=both_twodays_final
542 ! outfile="/sas/shared/mfritz/SAS/Data/auto_pay/daily_autopay/CS_reconciliation_data/reconciliation_data_&yesterday5..xlsx"
543 dbms=xlsx replace ;
544 sheet = "Onbothdays_twodaysago";
545

Error creating temporary file for XLSX file ->
/sas/shared/mfritz/SAS/Data/auto_pay/daily_autopay/CS_reconciliation_data//reconciliation_data_06MAR.$$1 . It is either not an
Excel spreadsheet or it is damaged. Error code=8000101D
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.13 seconds
cpu time 0.02 seconds

What do you think is the issue?

Thanks,
Sharda

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:14:38 GMT

Thank you for editing the email addresses...

The code is placed on the grid server as a scheduled job.

Sharda

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

Mark_sas — Wed, 08 Mar 2017 19:22:53 GMT

I would verify that you have available disk space in the outfile location you're specifying. You could also try redirecting to a different output location (with available space). If these don't help, I believe you're best served taking this one to Tech Support.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari — Wed, 08 Mar 2017 19:26:44 GMT

My first thought is to agree with Michelle, because I always suspect permission first.

Since you mention Grid, though, the other thing that comes to mind is a problem relating to shared file system:

1. Could jobs on different nodes be trying to access this file for update, and not being serialized correctly?

2. Could things be set up wrong, and this directory being incorrectly accessed for update by multiple grid nodes?

I will confess, I'm really spitballing here. It just seems like such an odd error if it's working correctly in EG.

Tom

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:28:56 GMT

Thank you all but I have confirmation that the service account has permission to that shared drive...

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:29:59 GMT

TomKari - what do you mean by your first point (specifically not being serialized)?

Thanks

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari — Wed, 08 Mar 2017 19:34:56 GMT

I'm probably using the terminology wrong, because I'm far from an expert.

But the job of the shared file server is to make sure that any disk resource (a directory name, a file name, a piece of physical disk) is created / updated by only one job, user, whatever.

If one job was busily writing the temporary file that's the source of an error message, and then another job was able to get a hold of the same resource for update, and make some changes, the first job would think that the file was corrupted. What should happen is the second job should be told that the resource is locked for update by another job.

As I say, spitballing.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

JuanS_OCS — Wed, 08 Mar 2017 19:35:18 GMT

Hello @sharda,

I have some questions:

- does it happen only on some jobs?

- does it happen with all the jobs that include a proc export?

- on the jobs that it happens: does it happens always you run them, or only sometimes?

- what happens if you select xls intead of xlsx?

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari — Wed, 08 Mar 2017 19:37:34 GMT

I should have added: If it works in EG in a non-grid environment, it leads me to wonder what's different in grid? And the shared file system is a very large elephant in the room.

Another thought. Can you run a test that works in the EG environment, and then run EXACTLY THE SAME TEST, same code, same data, same everything, on ONLY ONE GRID NODE? It would be interesting to see if it fails with the same message.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:55:59 GMT

Since we have only a production environment, i would need to wait until I can test this during our next outage but thanks for the suggestion.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 19:58:27 GMT

it happens specifically on the two jobs I added the snippet from which both included a proc export. Also, it happens sometimes but enough to stop the job from completing and the user being angry. I asked the user to test the xls instead but he is tied up at the moment.

Thanks though

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

JuanS_OCS — Wed, 08 Mar 2017 20:03:31 GMT

Ok, I see.

I have seen previosly similar kind or errors. The most common are related to:

- too large amount of rows on the exported excel

- incorrect template used

- some kind of problem on the storage, generally on shared storages, very common on Grid environments. On the direction of what @TomKari rightly indicated

The first 2 cases are easier to solve, but the last one usually requires intervention from SAS Consultancy services ot SAS Technical Support, because requires hands on investigation.

Let;s wait for the test done by your user. Thank you.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari — Wed, 08 Mar 2017 20:03:58 GMT

If it succeeds sometimes, and fails sometimes, with the same user, I don't see how it could relate to permissions unless that user has more than one credential.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

JuanS_OCS — Wed, 08 Mar 2017 20:05:49 GMT

In case that it succeds sometimes and sometimes fails... I agree with you. It might be more related to the GRID node where it is being executed. It might be interesting to know if there is such a pattern.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

TomKari — Wed, 08 Mar 2017 20:07:01 GMT

I really like that idea from @JuanS_OCS about the problem being a very large spreadsheet. That one should be quick to confirm or eliminate.

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 20:07:37 GMT

Thank you all

Re: Admin Coaches Corner set for March 8, 2:00-3:00pm ET

sharda — Wed, 08 Mar 2017 20:08:51 GMT

How can I help put you guys mind at ease? What test should I do?