Skip to Content
0
Former Member
Jan 06, 2011 at 08:10 PM

Stored Procedures called by Crystal 11.5 run 3 times with two instances

208 Views

I have a SQL stored procedure that is called by Crystal Reports. The report creates a permanent table that is used by another report so I am adding the Server Processor ID (@@SPID) as a field to identify a specific set of data. In troubleshooting this report, I ended up adding trace statements to the procedure that were written to another table. I found that the report ran the stored procedure 2 times before coming back and prompting for paramaters (parameters are in Crystal, not the stored procedure). These two times had the same SPID. Then after the prompts, it ran the procedure a third time with a different SPID. Not only does this then take 3 times as long to run, since the second report deletes the data with the specific SPID, one set of data is not getting deleted.

I then ran a couple of other straightforward reports and added the same trace statements to those stored procedures. In each instance I found that the stored procedure was run 2 times with the same SPID before the prompts and 1 time after with a different SPID.

Is there anyway to stop Crystal from running the stored procedure multiple times and how do I make sure I only have one SPID created?

In case it is pertinent, under Report Options I have made sure that both 'Verify on First Refresh' and 'Verify Stored Procedures on First Refresh' are not checked. And I"ve made sure that the same 2 are not checked under Options, Database tab. Under Report Performance Information, under processing, it has Require Two Passes as Yes, but I don't know what impacts that and how to make it only require one pass.

Can anyone give me any guidance in this area?

Thanks.