Hi All,
i Have An Script Which is Fetching Data From DB And sending Mail With the Data in Excel Format.
i'm Receiving Mail with Attachment when i Manually Running the script using ksh -x Scriptname or ./ Scriptname.
when i try to run it through cron i'm receiving mail only but Attachment is Not Present in that mail.
Please Suggest ASAP.
Could you please copy / paste the relevant parts of the script, or entire script if not too long, using code tags. Also, using code tags, copy and paste the crontab line. Otherwise, we're just making wild guesses. 
Most likely errors in these type of cases is related to environment.
We can help if you post the script.
Probably, the attachment ( uuencode? ) is not in PATH and you need an absolute path there.
This is just a guess.
hi Hanson,
script is,
### Local Variables Setup ###
MAIL_DL="USA.Birlasoft.VCRP.Support@xerox.com,USA.ISP.Critical@xerox.com"
TODAY=`date '+%d-%h-%Y'`
TODAY_TIME=`date '+%d-%h-%Y %H:%M:%S'`
ISP_RESPONSE="${APP_HOME}/tmp/ISP_error_response.log"
ISP_PATH="${APP_HOME}/shell_scripts"
report_sheet="Error_Details.xls"
ERR_CNT=`cat $ISP_RESPONSE|grep 'Total No of -6'|cut -f2 -d:`
### Inbuilt Function Created to send mail to the Distribution List ###
send_email()
{
( cat "$ISP_RESPONSE"; uuencode "$ISP_PATH/$report_sheet" Error_Details.xls; )|mailx -s "-6 Error Status on $TODAY" "$MAIL_DL"
}
### Local Environment Setup ###
if [ -r $APP_HOME/config/env ];
then
. $APP_HOME/config/env;
else
print `date +"%m/%d/%y %H:%M:%S"` " - Environment not available \n" >$ISP_RESPONSE
cat $ISP_RESPONSE | mailx -s ' Process failed during local environment Setup!!' $MAIL_DL
exit 1
fi
### Sql Variables Setup ###
SQL_SETS="
set serveroutput on;
set echo off;
set feedback off;
set heading off;
whenever sqlerror exit 1;
whenever oserror exit 1;"
SQL_SET="
set pages 50000;
set tab on;
#set colsep ' ';
set serveroutput on;
#set trimspool on;
set heading on;
set echo off;
set linesize 220;
set trimout on;
set feedback off;
set space 8;
set lines 32000;"
SQL_CNT_ERR_RECS="SELECT SUM(CNT)
FROM
(
SELECT COUNT(1) CNT FROM VCRADMIN.T523QUERY_RESPONSE_METRICS WHERE TRUNC(REC_TS)=TRUNC(SYSDATE) AND RESULT_CODE='-6'
UNION ALL
SELECT COUNT(1) CNT FROM VCRADMIN.T522REQUEST_RESPONSE_METRICS WHERE TRUNC(REC_TS)=TRUNC(SYSDATE) AND RESULT_CODE='-6'
);"
SQL_CNT_TOTAL_RECS="SELECT COUNT(1) FROM VCRADMIN.T006RAW_RESPONSE_RECORD WHERE TRUNC(REC_TS)=TRUNC(SYSDATE);"
### Connecting to the DataBase ###
CONN1=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SETS
$SQL_CNT_ERR_RECS
EOF`
echo "$CONN1" | grep ORA
if [ $? -eq 0 ]
then
echo "Failure: Issue during counting -6 from the DataBase" > $ISP_RESPONSE
cat $ISP_RESPONSE | mailx -s ' Process failed during -6 count!!' $MAIL_DL
exit 0
fi
CONN1=`echo $CONN1|sed -e 's/^ //g'`
### Connecting to the DataBase ###
CONN2=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SETS
$SQL_CNT_TOTAL_RECS
EOF`
echo "$CONN2" | grep ORA
if [ $? -eq 0 ]
then
echo "Failure: Issue during counting of total transaction from the T006 Table" > $ISP_RESPONSE
cat $ISP_RESPONSE | mailx -s ' Process failed during -6 count!!' $MAIL_DL
exit 0
fi
CONN2=`echo $CONN2|sed -e 's/^ //g'`
### Connecting to the DataBase ###
CONN3=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SET
spool $ISP_PATH/report.xls
COLUMN request_number HEADING 'REQUEST_NO'
COLUMN RESULT_CODE HEADING 'CODE'
COLUMN TRANSXTN_ID HEADING 'TRANSXTN_ID'
COLUMN TRANS_TYPE_CD HEADING 'RT'
COLUMN REC_TS HEADING 'DATE WITH TIME'
COLUMN REC_SRC HEADING 'SOURCE'
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T523QUERY_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE)AND RESULT_CODE='-6'
UNION ALL
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T522REQUEST_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE) AND RESULT_CODE='-6'
order by REC_TS desc;
spool off
EOF`
echo "$CONN3"> $report_sheet
echo "Total No of -6 : $CONN1 " > $ISP_RESPONSE
echo "Total No of Requests : $CONN2 \n\n" >> $ISP_RESPONSE
echo "Thanks" >> $ISP_RESPONSE
echo "VCRP Support Team \n" >> $ISP_RESPONSE
if [ $CONN1 -gt 0 ]
then
if [ $CONN1 -gt $ERR_CNT ]
then
send_email
fi
fi
---------- Post updated at 12:36 PM ---------- Previous update was at 12:33 PM ----------
Cron Entry is,
10 01 * * * ksh 'cd /apps/vcr/appl; . ./config/env;$APP_HOME/shell_scripts/isp_response_status_prod.ksh > /dev/null 2>&1 '
Could you also use the code tags by highlighting each code block and clicking that little square code button? Thanks.
Like this
And this
And this
Your script and your crontab entry both depend on $APP_HOME being set, but neither of them do anything to set that variable. The shell's current execution environment will not be available to cron when it runs your script, so you have to explicitly set the environment you need or replace the missing variables with hard-coded values.
Hanson pls look it now..
ISP_RESPONSE="${APP_HOME}/tmp/ISP_error_response.log"
ISP_PATH="${APP_HOME}/shell_scripts"
report_sheet="Error_Details.xls"
send_email()
{
( cat "$ISP_RESPONSE"; uuencode "$ISP_PATH/$report_sheet" Error_Details.xls; )|mailx -s "-6 Error Status on $TODAY" "$MAIL_DL"
}
SQL_SET="
set pages 50000;
set tab on;
#set colsep ' ';
set serveroutput on;
#set trimspool on;
set heading on;
set echo off;
set linesize 220;
set trimout on;
set feedback off;
set space 8;
set lines 32000;"
CONN3=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SET
spool $ISP_PATH/report.xls
COLUMN request_number HEADING 'REQUEST_NO'
COLUMN RESULT_CODE HEADING 'CODE'
COLUMN TRANSXTN_ID HEADING 'TRANSXTN_ID'
COLUMN TRANS_TYPE_CD HEADING 'RT'
COLUMN REC_TS HEADING 'DATE WITH TIME'
COLUMN REC_SRC HEADING 'SOURCE'
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T523QUERY_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE)AND RESULT_CODE='-6'
UNION ALL
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T522REQUEST_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE) AND RESULT_CODE='-6'
order by REC_TS desc;
spool off
EOF`
echo "$CONN3"> $report_sheet
What about $APP_HOME like Don Cragun suggested? Do you try hard-coding that in your crontab?