Cannot create a worktable row larger than allowable maximum.

[colmadoyle]

Why does this happen?

And what does it mean?

"Cannot create a worktable row larger than allowable maximum.
Resubmit your query with the ROBUST PLAN hint."

Thanks (newbie here!)

col

[Tony Rogerson]

Can you post your query here Col.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]

"colmadoyle" <colma...@yahoo.com> wrote in message
news:4bfeb86.02030...@posting.google.com...

[Luke Amery]

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.

cheers,
Luke Amery

"colmadoyle" <colma...@yahoo.com> wrote in message
news:4bfeb86.02030...@posting.google.com...

[Colm Doyle]

The Query was just a simple select statement. Something like

Select * from MyTable.p_SomeCrazyField where
PKey='5572EAD8-09F4-47C0-83B7-00C4E4ADC864'

Is it because of the long p key?

Do I set ROBUST PLAN in the select statement or as a parameter on the
DB?

What's it's syntax?

Thanks, BTW!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Luke Amery]

SELECT * FROM MyTable.p_SomeCrazyField WHERE PKey =
'5572EAD8-09F4-47C0-83B7-00C4E4ADC864' OPTION(ROBUST PLAN)

as to why it is happening?? I honestly don't know.. I have done plenty of
queries like this without weird plan failures... maybe give sql2k sp2 a go??

cheers,
Luke Amery

"Colm Doyle" <colma...@yahoo.com> wrote in message
news:uaX#mRRxBHA.2588@tkmsftngp07...

[Colm Doyle]

Can I change the worktable row size with an sp command?

C