ASP 3.0 Error on SQL Server 2005

----
Microsoft OLE DB Provider for SQL Server error '80040e14'
The query processor ran out of internal resources and could not produce a
query plan.
This is a rare event and only expected for extremely complex queries or
queries that reference a very large number of tables or partitions.
Please simplify the query. If you believe you have received this message in
error, contact Customer Support Services for more information.
----
Has anyone else seen the above error message in an Active Server Page 3.0,
running on top of SQL Server 2005?
(The database was restored to a new 2005 database with a complete backup
from the 2000 database.)
The query that causes this error has run fine in an ASP 3.0 on top of SQL
Server 2000 on the same database, on an older Windows server.
The query runs fine in SQL Server 2005 Management Studio (and SQL Server
2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
the old Dell server with SQL Server 2000.
I used the 2005 Database Engine Tuning Wizard, and applied the suggested
indexes. That did not help. The query still runs in 15 seconds in
Management Studio and still breaks in the ASP.
Any ideas, anyone?!
JimJim Moon (please.reply@.group) writes:
> Has anyone else seen the above error message in an Active Server Page 3.0,
> running on top of SQL Server 2005?
> (The database was restored to a new 2005 database with a complete backup
> from the 2000 database.)
> The query that causes this error has run fine in an ASP 3.0 on top of SQL
> Server 2000 on the same database, on an older Windows server.
> The query runs fine in SQL Server 2005 Management Studio (and SQL Server
> 2000 Query Analyzer too). It returns 556 rows. It runs in 15 seconds on
> the new Dell PowerEdge 2850 with SQL Server 2005. It runs in 3 seconds on
> the old Dell server with SQL Server 2000.
> I used the 2005 Database Engine Tuning Wizard, and applied the suggested
> indexes. That did not help. The query still runs in 15 seconds in
> Management Studio and still breaks in the ASP.
Without knowing the tables or indexes, it is difficult to say. But, one
thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
the database on SQL 2005?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9771753A2067Yazorman@.127.0.0.1...
> Jim Moon (please.reply@.group) writes:
> Without knowing the tables or indexes, it is difficult to say. But, one
> thing: did you run UPDATE STATISTICS WITH FULLSCAN when you had restored
> the database on SQL 2005?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Thank you, Erland!
UPDATE STATISTICS <table_name> WITH FULLSCAN
did the trick.