Posted by soulkitchen on 08/22/06 15:43

soulkitchen wrote:
> soulkitchen wrote:
> > 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.
>
> That did not seem to fix it. It am pretty sure it is a Crystal problem
> though. The SP runs fine in query analyzer

I hope someone can answer this question.... The problem above was
happening, and I did not do anything to fix it, but the next day the
problem went away. I can't imagine what could have happened over night
to fix my problem. I do transaction log backups on the hour, and a full
database backup once a week, but no last night.

[Back to original message]