In our project we have a requirement to execute Crystal Report with stored procedure as their data source. Here we have used the Crystal Report XI as the reporting tool and Sybase as the database. The problem is that for each time when the report is executed, the stored procedures are getting executed twice. Due to this reason reports are taking more time to execute. We have also found that this problem exists with the XI version of the crystal report only. We tried the same with crystal report version 7 and found that the stored procedure gets executed only once. For this issue we have already installed the service pack 3 for Crystal Report XI. But the issue is not resolved yet. We stuck with this problem for a long time but till now we have not got any solution. Any help in this regard is highly appreciated.
If you look up KBase 1479697 on the support site, does it apply to you?
Note that Crystal Reports XI Release 2 is a free upgrade if you have Crystal Reports XI Release 1.
Are you executing the stored procedure directly from the report? I have found that I have needed to put the stored proc in a subreport and pass values from the parameters in the main report to the parameters in the stored proc (assuming you have parameters, of course). If you can tell me more about the design of your report, I may be able to help more.
The parameters in my report are getting the values I want. But once I run my report through my project the procedure doesn't return any data.
By debuging it I could find out that my procedure was executed twice. The first time with values, the second with no values. I don't know why...When I input the values manually in crystal reports, it returns what is expected, but when running through the application, I find myself in this procedure executed twice issue.
Andrea, I followed what you've said. But even using a subreport and sharing the parameters, the behavior of my report is still the same.
Could any of you shed some light on this, please?
ps: I've unchecked the boxes "verify on first refresh" and "Verify Stored procedures on first refresh". Manually it's still working, but through application it's executed only once, but with no values returned.
I've solved the problem around here.
1 - Uncheck the "Verify Stored procedures on first refresh" on report options.
2 - Make sure your procedure is receiving the correct data type as input parameters. In my case, the data coming from ASP was String to a String input parameter, but inside the procedure I was comparing the result to a number type field. Even parsing the string to number, It wasn't able to execute (return what was expected). So, I've changed those parameters to Number in ASP (int) and in the procedure (NUMBER), and worked fine. Funny how it worked through SQL-Plus and Crystal.
3 - About having no values as return, there's something very weird I've found out. At some points in my procedure I had some "SELECT <field> INTO <variable> FROM <table>;", and when I executed the procedure I received empty values as return; that's because it was a "NO_DATA_FOUND" exception. But, once more I had no problems executing it through Crystal or SQL-Plus.
Without raising that exception I received the message "ORA-24338: statement handle not executed". Funny thing is that when I've changed my "SELECT INTO" statements for a "FOR x IN (SELECT ...) LOOP (variable) := x.(value); END LOOP;", it worked fine.
Well, those are some points that solved my problem, the SELECT change (point 3) is most likely a workaround...But anyway, I hope it helps.