The background of the issue is in older versions of PeopleSoft SQR programs used to execute from a client workstation and there was no 4-tier concept, so People used to hardcode the output file path with in the program.

PeopleSoft 8 onwards reports are being posted to web and people can view the reports in the view log trace.

But for custom data files SQR uses the OPEN FOR WRITING and WRITE commands for creating these files and control where the output goes.  Since SQR is controlling the output vs the Process Scheduler or PSSQR controlling the output, even if it is written to the right directory, the Report Distribution Server may be unaware of it’s presence and will not transfer and generate a link to view this output. 

To overcome this issue one of the following solutions can be adopted.

With PeopleTools 8.4 onwards the following option can be used. Make sure that the supported file extensions (like .pdf, .lis, .txt are used.).

Option 1:
Use the FILEPREFIX variable from the delivered SETENV.SQC to build the directory path for the hard coded file name. Make sure that SETENV.SQC is coded at the top of the program. Otherwise it will give a substitution variable error.

Example:

  LET $filename = ‘{FILEPREFIX}’||’bank1.txt’
  OPEN $filename AS 1 FOR-WRITING RECORD=85:FIXED
Option 2:
Use the following code format.

Example:

let $filepath = ‘[PS_HOME]\appserv\prcs\[databasename]\log_output\SQR_’ || $ReportID || ‘_’ || $prcs_process_instance || ‘\’
let $filename = $filepath || ‘datafile.lis’
open $filename as 1 for-writing record=100

if the above code doesn’t work for you, you can do one more thing.

You can query PSPRCSPARMS table for PRCSOUTPUTDIR field for your $prcs_process_instance and substitute output value as your file path

Example:
SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = $prcs_process_instance

The below error was came up when one of my colleague was trying to truncate a table with in an SQR procedure. I don’t know whether this is a Sybase specific or for all the databases.

SQL Error  = (226) TRUNCATE TABLE command not allowed within multi-statement transaction.
The Solution is to COMMIT before and after the truncate table statement, So that this will commit the previous transactions.

Example:

Before:

TRUNACATE TABLE PS_CUSTOMER_TMP;

After:

COMMIT;
TRUNACATE TABLE PS_CUSTOMER_TMP;
COMMIT;

(SQR 4045) Function or operator ‘dateadd’ requires date argument. The above error will show up if you use dateadd function in SQR and didn’t declare the variable as date. use the following example. declare-variable
date $sunday
end-declare
….let $date1 = dateadd($sunday,’minute’,1)