Posted by Emin on 09/13/07 14:46

On Sep 12, 6:03 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Emin (emin.shop...@gmail.com) writes:
> > The really strange part is that if I left the clause "ORDER BY myDate"
> > off the query I didn't get the error. My work around was to put the
> > results of the query into a temporary table called #myTemp and then do
> > "SELECT * FROM #myTemp ORDER BY myDate".
>
> > 1. Is there a way to tell the query processor to use more resources so
> > I don't get this error?
>
> No. Possibly more memory to SQL Server could help.

The maximum memory is currently set to 2 GB. Is there some special
memory setting for the query analyzer?

> > 2. Since it seems like the ORDER BY clause is causing the problem, is
> > there a way to tell the query analyzer to just do the query and then
> > order everything instead of trying to be too clever?
>
> Yes, leave out the ORDER BY clause. If you leave it in, the query
> processor has to comply. Or tell you that you can't. It can't produce
> an unordered result when you requested an ordered one. That would
> be a bug.

What I meant was, is there a way to tell the query analyzer to first
produce an unordered result and then to sort it? My guess is that the
query analyzer is trying to keep things in sorted order during the
processing and this is what makes it run out of resources. The only
thing the temp table is doing is forcing the query analyzer to break
things into these two steps (1) do the query (2) then sort the
results. It seems like there should be a way to tell the query
analyzer this more directly.

Thank you very much for your answers.

Sincerely,
-Emin Martinian

[Back to original message]