Web Development

Ask something
Discussion Locked

SQL Server Error

0 helpful
4 replies

by munrrob

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am writing a report using Business Objects looking at an SQL SErver 2K database. I am getting the following SQL error:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.510"

Any ideas what could cause this?

4 replies

by sachin

BOL says it all:

ROBUST PLAN -

Forces the query optimizer to attempt a plan that works for the maximum potential row
size, possibly at the expense of performance. When the query is processed,
intermediate tables and operators may need to store and process rows that are wider
than any of the input rows. The rows may be so wide that, in some cases, the
particular operator cannot process the row. If this happens, SQL Server produces an
error during query execution. By using ROBUST PLAN, you instruct the query optimizer
not to consider any query plans that may encounter this problem.

so, you are encountering a condition where sql server is choosing a plan, when
that plan is used to try and get results, it turns out the plan is not acceptable
as assumed bounds are surpassed, so the query fails, so when you add the ROBUST
PLAN option as a query hint to your query, the plans where this happens are
discarded, which means a slower plan that is guaranteed not to surpass the assumed
bounds is selected.

Alos make sure you have give sql2k sp2 a go??

This discussion is closed to new replies.