Error on insert into a SQL Server 2000 table - Cannot create a worktable row larger than allowable maximum

have a look at this

http://forums.databasejournal.com/showthread.php?t=33405

Ran query like this:

insert into meeting_notes (meeting_id, note_type_id, participant_id, createdate, createuser, reviseddate, note)
select meeting_id, 2 as note_type_id, admin_partid as participant_id, getdate() as createdate, dbo.f_getbadge(admin_partid) as createuser, getdate() as reviseddate, admin_notes as note
from meeting_clientadmin
where admin_notes is not null and admin_partid is not null and meeting_id not in (15811, 701790)
OPTION(ROBUST PLAN)

Results:
Server: Msg 8619, Level 16, State 2, Line 1
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.

I can't use a text column because we're switching from text to varchar to take care of performance issues.

you will have to make the note field into a dedicated table, along with the (a unique) note_id field, to ensure the table has < 8060 bytes row definition

Explain what you mean by a dedicated table?  I'm doing an import from other tables into this new table, and I need the other fields on the insert.  On the new table, we're using an identity field as a table key.

I'm really trying to find a way to get around having to set the column to a width of 7900 characters, which is what SQL Server is trying to force me to do.

8096 is the maximum size for the whole row in sql server. The problem is that one note plus the other fields in the select might be larger than the maximum size. Try to truncate the note field or to reduce it's size see if it helps.

Are your notes ever really 8000 characters?

On your select try casting the notes as a varchar 7900.

cast(admin_notes as varchar(7900)) as note

If it is actually truncating the record, you should get an error.

LEFT(admin_notes,7900) in the select list  would be enough. It is not truncating because both fields, source and destination, are of the same size, 8000, the total row size is the problem.

Yes, my notes are over 8000 characters.  That's why some of the old data is getting truncated.

I wanted to get around losing any data from the notes field; this would necessitate redesign.

>I wanted to get around losing any data from the notes field; this would necessitate redesign.
yes, with sql 2000 you have to redesign if you don't want to lose data by splitting the notes field into several parts.

with sql 2005, you would simply use VARCHAR(MAX) and you have all the advantages from the normal varchar data type, but the capacity of 2GB from the TEXT data type.

We're not using SQL Server 2005 yet, and are not planning to upgrade soon.

"I can't use a text column because we're switching from text to varchar to take care of performance issues."

You might be addressing a performance problem with the wrong solution. Switching to varchar may not help you and the varchar data type may not be your performance problem.

Too late now to worry about whether changing from text to varchar 8000 will help performance problems.  Already done the work on this and it's in testing stages now.  I might have to change the column to varchar 7900 instead of 8000 if there's no other solution.

I was still getting the size limit warning, so when I do the import, I need to do it using 7900 character size chunks instead of 8000.

It worked using the temp table and the 7900 characters...no warnings.