Posted by soulkitchen on 08/19/06 19:27

Erland Sommarskog wrote:
> soulkitchen (esoulkitchen@gmail.com) writes:
> > After I change a fairly complex stored procedure and I run a report
> > against it, crystal hangs at "assesing database". I have verified the
> > database. When I run a trace on SQL is shows repeated cachemiss over
> > and over. I let it run for 30 minutes and nothing.
>
> The first reaction is that this is a Crystal problem. Then again, if
> SQL Server keeps generating CacheMiss over and over again, and nothing
> comes in from the client, then that would be a bug in SQL Server.
>
> Actually, I have seen that precise behaviour, but it was in an early
> beta version of SQL 2005 and it occurred when ANSI_PADDING was off.
>
> It sounds as if this is very difficult to troubleshoot on a distance. But
> is it possible for you to attach a trace file, with all SP and TSQL
> events enabled. Adding the Error events may be a good idea as well.
> It could help if you posted the procedure as well.
>
> And of course the version of SQL Server you are using.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.

" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".

I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.

[Back to original message]