Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Scott Centille]

I am getting the following error when running an asp page with Sql
Server 2000 that works with Sql Server 7.0 :

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
larger than the page size into a hash table. Resubmit the query with
the ROBUST PLAN hint.

However, when resubmitting the query with the ROBUST PLAN, I get the
following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The query
processor could not produce a query plan from the optimizer because
the total length of all the columns in the GROUP BY or ORDER BY clause
exceeds 8000 bytes. Resubmit your query without the ROBUST PLAN hint.

Has anyone else had the same problem?

[Umachandar Jayachandran]

How wide are the columns in the GROUP BY? You are probably exceeding the
8000 bytes limit. Try to simplify your query first, grouping on large number
of columns (wide ones too!) will reduce performance.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[Scott Centille]

"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message news:<OP6lXFNLBHA.1916@tkmsftngp07>...

> How wide are the columns in the GROUP BY? You are probably exceeding the
> 8000 bytes limit. Try to simplify your query first, grouping on large number
> of columns (wide ones too!) will reduce performance.

There are only two integer fields in the columns for the GROUP BY,
which are definitely much less than the 8000 bytes limit. That is the
confusing part.

[Umachandar Jayachandran]

What does your query look like? What are the columns in the SELECT list?
Is the GROUP BY part of a main SELECT? I would check the query plan first to
see if SQL Server is using some worktables. This is a good starting point to
debug this.

[Dan Lemon]

I was getting the exact same problem. In my case, it was caused by
using the min function on a column that I needed to cast to an INT.

I am using SQL Linked Servers to do queries against content indexed by
Index Server (using Index Server's OLEDb interface).

When I used the min function on one of the custom indexed properties,
I received the same error that you are getting.

The fix was to cast the custom property before using the min function

Old query:

SELECT chapterid, min(pagenum) minpage FROM OPENQUERY(...) group by
chapterid

Fix:

SELECT chapterid, min(cast(pagenum as int)) minpage FROM
OPENQUERY(...) group by chapterid

Hope this helps...

scott.c...@bfpna.com (Scott Centille) wrote in message news:<b3358825.01082...@posting.google.com>...