I have a Stored Procedure that takes a single input string parameter (and a ref cursor output parameter) in my report. The input parameter is fixed 'rpt1' so I don't have a parameter variable or need to prompt for it - just a quoted string in the SQL calling the stored procedure. The stored procedure executes when I first set up the report, however, subsequent executions and refresh, and the stored proc does not execute.

 

The stored procedure updates the last run date of the specified report [rpt1] in this case. this report needs to run with no user intervention so a parameter prompt will not work - unless there is a way to force it to always use a default value.

 

any ideas, suggestions?

 

 

Command object:

 

DECLARE
  MyRptCur SYS_REFCURSOR;
BEGIN
  TMP_UPDT_LAST_RUN_PROC('rpt1', MyRptCur);
END;

 

CR2011, v14.

Funny you should ask. I forgot to mention this, but I have not been able to figure out how to display the return data. As mentioned above, the Stored Procedure has 2 parameters (string IN, refcursor OUT). When I set up the Crystal Report and put the call to the Stored Procedure in a Command object, I declare a refcursor, but am not sure how to include it on my report. 

 

I have also tried setting up a report with the Stored Procedure directly set up as my data source (no command object). This approach allows me to specify refcursor data fields on the report, but at runtime I am prompted to set the string input variable. I have to be able to have this report run with no user intervention. The input parameter will always be the same value. I tried setting a default for the parameter but am still prompted.

NOTE: This report does execute the stored procedure every time (because the input parameter is being set each time?), but this will not work for our needs of a stand-alone report.

 

I appreciate suggestions, etc.

 

 

 

We are replacing InfoReports with Crystal Reports. We have reports that are scheduled to run daily and some weekly. After a report runs, a post-run rpt runs that updates Last Run Date/Time specific to the scheduled report. The next time the report runs, it includes data added to the db since the last run time for that rpt. So I am trying to create these post-run rpts that will call a stored procedure and pass the rpt name so the last run table can be updated. The only thing the users see is a report printout when they come in in the morning. If there are problems executing the report, the post-run rpt will not update the last run so the next time it runs it still includes all data newer than the last time it ran.

 

If I try to hard-code the report name parameter and call the stored proc inside a command object, it does not execute so I am trying to figure out a way to make crystal think the parameter variable has been set.

 

 

 

 

 

Thanks,

Bill

 

- Bill