Crystal no loading SQL stored proc data.

[TOraptor]

I have a simple rpt in crystal reports and i bound it to a SQL stored procedure.

The stored procedure calls two other stored procs within itself and then finally selects from a temp table formed at the end of the execution of the two sub procs to yield the result that will be displayed in the report.

The stored procedure executes fine. It displays the data when i run it in SQL query analyser. Since there are a few lookups performed the stored procedure takes a few seconds to prepare the resultset. But it does work for all kinds of parameters passed in. And also that in the stored procs i am using temp tables to store the data which i did not use before in other reports that i have made so far. Finally i do a select from that temp table hoping crystal will pick the result up from memory.

I am using Crystal reports advanced developer edition 10. The report is a plain simple report and does no formatting or formula customization to the result that comes in.

I am connecting thru an ADO OLE DB connection to the datasource using the SQL driver and then use the database expert to load the stored proc into the report.

I see the fields that i need and then i try to browse the data in them. I am prompted to enter the two parameters which my stored proc takes. (it works in SQL query analyser for these same parameters). But I see blank result for each field.

I have set the NOCOUNT ON

But sometimes when i let the report sitting there for some time i notice that after a while, the data (in Browse data..) for any field, appears suddenly but it disappears again upon refresh.

Please suggest what you think could be the problem. It seems it does not work instantaneously but only if i leave it idle for some time.

 

[synapsevampire]

It sounds like the data is still being returned, not that it's failing, since it does return data eventually.

You might try another form of connectivity, such as direct or ODBC to see if that helps.

-k

 

[TOraptor]

I tired ODBC DSN with the same result. A blank result. Although it does make sense that the data does load eventually but i have seen in my past reports that the report generally should wait for the data to be prepared by the datasource before rendering.
IN this report's case i am totally drawing a blank.