RE: [sqr-users] unix email in SQR

[Bob Stone <bstone@fastenal.com>]

Yes.  this is true.  Here's a copy of what's sent to the OS, the '$command'
variable (between the +++'s).  notice where the (echo " ... ") is.
Everything between those two " is the $body. 

++++++++++++++++++++++++++++++++++++++++++++++++++++++

(echo "BUs were added to Inventory Interfaces on 03-03-2005. 

-----

MNSAK - added to fas_intfc_ty.  not PS live.
     REGION: 500, Winona - Steven A Rucinski.  DISTRICT: 0416, Gary G
Branstner. 
TNNA1 - added to fas_intfc_ty.  PS live.
     REGION: 1000, Atlanta - Steven J Roembke.  DISTRICT: 1026, Steven L
Moore. 
SKEST - added to fas_intfc_ty.  PS live.
     REGION: 1200, Canada - Kenneth R Nance.  DISTRICT: 1006, Erik
McCluskey. 
OKCLA - added to fas_intfc_ty.  not PS live.
     REGION: 1100, Dallas - James C Jansen.  DISTRICT: 1117, Patrick L
Jolliff. 
ONGOD - added to fas_intfc_ty.  PS live.
     REGION: 1200, Canada - Kenneth R Nance.  DISTRICT: 1012, Maximilian
Bezner. 

-----

MNSAK - added to ps_fasintfl_lock for multiproc.
TNNA1 - added to ps_fasintfl_lock for multiproc.
SKEST - added to ps_fasintfl_lock for multiproc.
OKCLA - added to ps_fasintfl_lock for multiproc.
ONGOD - added to ps_fasintfl_lock for multiproc.

-----

MNSAK - added to ps_est_int_ctl for estimator.
OKCLA - added to ps_est_int_ctl for estimator.
ONGOD - added to ps_est_int_ctl for estimator.
SKEST - added to ps_est_int_ctl for estimator.
TNNA1 - added to ps_est_int_ctl for estimator.
") | mailx -s "New BUs added to PeopleSoft Inventory Interfaces."
bstone@fastenal.com
Error sending e-mail to: bstone@fastenal.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Knapp, Richard
Sent: Friday, March 04, 2005 2:26 PM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: RE: [sqr-users] unix email in SQR


When you do the show on the command you are sending off to the OS, it
should show only the actual text you are sending and should not contain
any variable names.  Maybe this is what you are doing but giving us a
shorthand version in your email to the group.  Can you send the exact
text produced by the show $command in your last procedure?

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Bob Stone
Sent: Friday, March 04, 2005 2:18 PM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: RE: [sqr-users] unix email in SQR

if i get what you're saying, you think the entire command should be one
string?  Because that is what I'm doing.  I go through the entire main,
constructing a $new_bu_email_body string so that it looks rights, then
in
the email (the last step of the program) i concat that into one big
string
($command) and send command to the unix box.

let $command = '(echo "' || $body || '") | mailx -s "' 
                    || $subject || '" ' || $email_address

otherwise, I'm not sure I understand your advice.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Knapp, Richard
Sent: Friday, March 04, 2005 2:05 PM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: RE: [sqr-users] unix email in SQR


Yeah.  There's definitely something wrong with the string you are
passing.  You want sqr to interpret and substitute the $body and
$subject variables before the string is constructed and sent off to the
OS.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Bob Stone
Sent: Friday, March 04, 2005 1:56 PM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: [sqr-users] unix email in SQR

I'm having a problem having my Unix machine send mail messages from SQR,
and
I dont understand why.  I know that all of my select logic and program
logic
is correct.  The program does everything it's supposed to except send
the
message to us to let us know it did it.  In the log message I get the
error
'Error sending e-mail to: bstone@fastenal.com'  ...which is the message
I
make with the 'show' statement when the #unix_status returns some non-0
value (error value).  

In my opinion, this error has something to do with the string that I'm
passing to the

I had this working before in our production environment, but it stopped
working when I started using the DIST_REGION_INFO procedure.  In my
opinion,
this error has something to do with the string I pass into the
body...because that's the only part that's changed since it was working.
the command I pass is:  (echo "body") | mailx -s "subject"
bstone@fastenal.com

Does someone see something wrong with the string I'm making?  Are there
some
sort of protected Unix characters that I'm messing with inside the
"body"
section?  please let me know if you have any ideas.

Thanks

Bob Stone  
Fastenal Inventory Developer

Here's a shortened version of the code:
!*******************************************************************
begin-procedure MAIN
!*******************************************************************
ENCODE '<13>' INTO $cr
let $new_bu_email_body = 'BUs were added to Inventory Interfaces on ' 
     || $current_date || '. ' || $cr || $cr || '-----' || $cr || $cr

BEGIN-SELECT
t.business_unit     &t.business_unit
     let $business_unit = &t.business_unit
     if **case one
          let $ps_live = 'Y'
          let $new_bu_email_body = $new_bu_email_body ||$business_unit
|| '
- added to fas_intfc_ty.  PS live.' || $cr
     end-if
     if **other case
          let $ps_live = 'N'
          let $new_bu_email_body = $new_bu_email_body ||$business_unit
|| '
- added to fas_intfc_ty.  not PS live.' || $cr
     end-if
     do dist_region_info($business_unit)  !possibly something in this
procedure??
     do other_processing
FROM sysadm.some_tables
WHERE {$clause}
END-SELECT

! there are 2 other selects which add to $new_bu_email_body in the exact
same way as above. not the problem.

if $new_bu_found = 'Y'
     let $email_subject = 'New BUs added to PeopleSoft Inventory
Interfaces.'
     do email-message('IN', 'FASIN176', $email_subject,
$new_bu_email_body)
end-if
end-procedure MAIN

!*******************************************************************
begin-procedure dist_region_info($bu)
!*******************************************************************
begin-select
dist            &district
dist_descr      &district_descr
reg             &region_code
reg_descr       &regional_descr
     let $dist = &district
     let $dist_descr = &district_descr
     let $reg = &region_code
     let $reg_descr = &regional_descr
     let $_new_bu_email_body = $_new_bu_email_body || '     REGION: ' ||
$reg  || ', ' || $reg_descr || '.  '
     let $_new_bu_email_body = $_new_bu_email_body || 'DISTRICT: ' ||
$dist
|| ', ' || $dist_descr || '. ' || $_cr
from sysadm.ps_fas_rmdm_vw
where business_unit = $bu
end-select
end-procedure dist_region


!*******************************************************************
begin-procedure email-message($module, $prog_name, $subject, $body)
!*******************************************************************
begin-SELECT 
email_address 
      let $email_address =ltrim( rtrim(&email_address, ' '), ' ')
      let $command = '(echo "' || $body || '") | mailx -s "' || $subject
||
'" ' || $email_address
      show $command
      call system using $command #unix_status   
      if #unix_status <> 0
        show 'Error sending e-mail to: ' $email_address
      end-if 
from ps_fas_email_tbl
where {$clause}
end-select
end-procedure



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users