Insert statement raising 8626 error.

[MarkButler]

Why does the following insert statement produce the error that follows the code.

INSERT INTO diary
   (
   di_user, di_type, di_task_dt, di_subject,
   di_rem_fl, di_sta_cd, di_sta_dt, di_notes,
   di_dest_pk, di_dest_fm
   )
   SELECT
         froi.fr_efroi,
         'A',
         dbo.fn_business_days(froi.fr_mco_dt, 3),
         'FROI received on ' + CONVERT(VARCHAR(8), froi.fr_mco_dt, 1) + ' is still in new status.',
         'N',
         'OP',
         GETDATE(),
         [COLOR=red]'Claim: '+froi.fr_claimno,[/color]
         froi.fr_mco_itn,
         'FROI'
      FROM
         froi
         LEFT JOIN diary 
         ON LEFT(diary.di_dest_pk,20) = froi.fr_mco_itn
      WHERE
         diary.di_dest_pk IS NULL AND
         froi.fr_edi_sta = 'N' AND
         froi.fr_mco_dt < @a_date

Error:

Server: Msg 8626, Level 16, State 1, Line 10
Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

The line in red is the one actually causing the error message. If I change it to "'Claim:'," it works.

TIA

 

[TheJon]

What is the datatype of froi.fr_claimno?


[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[MarkButler]

Thanx for the response.

The fr_claimno is defined as follows: fr_claimno [char] 10 NOT NULL

The field being updated with that information is defined as: [DI_NOTES] [text]

Am I breaking a rule by inserting data into a field defined as [text]?

 

[TheJon]

I think that it is creating a work table based on the destination definition (text, maximum length 2^30-1 = 1,073,741,823 characters) and it won't allocate this space.

Does any of your data currently in [DI_NOTES] exceed the 8000 char/varchar limit? If not then redefine your target table with this datatype. I can't think of a workaround, maybe someone else can?

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[MarkButler]

What is interesting is if I change the line:

'Claim: '+froi.fr_claimno,

to

''

or

'Claim: '

the query runs.