Truncate error on Update Statement 1

[Catadmin]

Okay, this is weird. I have a table with a lot of fields and 1 single record on which I'm having my "Data will be truncated" error. The single record has many NULL values and those fields that are not null add up to be approximately 465 characters. I'm using default SQL Server collation on 2005. The field having the truncation error (Comment) is Varchar(1500). The actual character length of the current value in the field is 138. So, I should have plenty of space in which to append another small character comment, right?

   Update t1
    Set Status = 'A', ModifiedOn = GetDate(), 
    ModifiedBy = 'MyAuto-SQLJob', 
    Comment = ltrim(rtrim(isnull(t1.Comment,'')) + '. Status Modified per Ticketxxxxxx')
    from table1 t1
    left outer join table2 t2
    on t1.PKID = t2.PKID
    where t1.Status = 'R'
    and t1.PKID not in (Select PKID from table2)

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

The above error is occurring on the Comment field, I've tested the code with variations to verify this. But the Comment field for this particular record has plenty of space in it...

Does anyone have any idea why this might be occurring? It's driving me nuts.

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[arrowhouse]

Can you let us know what type and length the comment column is?

 

[Catadmin]

Arrowhouse,

I did actually list it in my post, but to reiterate, Comment is Varchar(1500).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[arrowhouse]

Sorry completely missed the first para!

Can you run:

SELECT MAX(LEN(Comment))
FROM table1 t1
left outer join table2 t2
on t1.PKID = t2.PKID
where t1.Status = 'R'
and t1.PKID not in (Select PKID from table2)

 

[Catadmin]

The result is 138.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Not to state the obvious, but is ModifiedBy large enough to accmodate 'MyAuto-SQLJob'?

Knowing you, I'm sure it is.

I re-wrote your update so that it selects the comments as a derived table. Then joins with the original table to do the update.

As always, I suggest you create a backup before running this query.

Update t1
Set    Status = 'A', 
       ModifiedOn = GetDate(), 
       ModifiedBy = 'MyAuto-SQLJob', 
       Comment = A.Comment + '. Status Modified per Ticketxxxxxx')
From   (
        Select t1.PKID,
               LTrim(RTrim(IsNull(Comment, ''))) As Comment
        from   table1 t1
               left outer join table2 t2
                 on t1.PKID = t2.PKID
        where  t1.Status = 'R'
               and t1.PKID not in (Select PKID from table2)
       ) As A
       Inner Join Table1 T1
         On A.PKID = T1.PKID

If this works (where the other one didn't) then I have a theory about why. Let me know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

ModifiedBy is Varchar(15), which should allow the 13 character string I put in. Also, I tested the Update statement by individually commenting out the different fields I was setting. It only worked when I commented out the "Comment =" part of the statement, which tells me that the rest of it is fine.

Okay, George... I tried your query on a Dev db and it worked. Now tell me your secret or I'll hang your cat from a flagpole! @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Not a secret. A theory.

You said that Comment is VarChar(1500). I suspect that you have data in some record(s) where you are close to that limit. SQL Server is probably checking the max length of that field and comparing it to the new value (with the added sentence) to see if it will fit.

By using a derived table, SQL only checks the data in the derived table to make sure (when adding the extra text) that it will fit.

If my theory is correct, then this query should return something close to 1500.

SELECT MAX(LEN(Comment))
FROM table1 t1

BTW: I do have a cat, but I don't like him, so feel free to hang him anyway.

Disclaimer: No cat or any other animal was harmed during the composition of this reply.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

1496 is the result of your query, so your theory is probably correct. In essence, it's looking at the biggest record in the table instead of just the records I want to modify.

Thank you so VERY much. Star for you, Pounce treats for your cat.

Disclaimer: No Stars or kitty treats were actually consumed during the composition of this reply.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLSister]

George I give you a star too as I learned something new myownself.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Catadmin]

Okay, this is down right aggrivating. When I restore last night's production db down to dev, I can get this new code to work. When I update the proc on Production and run it, I get the same damn truncate error.

And it's failing on the same damn record....

Any other thoughts?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

CatAdmin,

I was playing around with my 'theory' and I can't seem to duplicate the problem. It's strange, that's for sure. The new query works in dev but not production (even with last night's backup). I have 'something else' I'd like you to try.

Back top the original query, try adding this...

Update t1
    Set Status = 'A', ModifiedOn = GetDate(), 
    ModifiedBy = 'MyAuto-SQLJob', 
    Comment = [!]Left([/!]ltrim(rtrim(isnull(t1.Comment,'')))[!], 1450)[/!] + '. Status Modified per Ticketxxxxxx'
    from table1 t1
    left outer join table2 t2
    on t1.PKID = t2.PKID
    where t1.Status = 'R'
    and t1.PKID not in (Select PKID from table2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Actually, I finally got it to work when I separated out my Update statement into two update statements. Annoying as it is, I had to do the following:

Update t1
Set Status = 'A', ModifiedOn = GetDate(), 
ModifiedBy = 'MyAuto-SQLJob'    
from table1 t1
on t1.PKID = t2.PKID
where t1.Status = 'R'
and t1.PKID not in (Select PKID from table2)

Update t1
Set Comment = ltrim(rtrim(isnull(t1.Comment,'')) + '. Status Modified per Ticketxxxxxx')
from table1 t1
on t1.PKID = t2.PKID
where t1.Status = 'R'
and t1.PKID not in (Select PKID from table2)

This is just weird. And annoying. Thanks for the help, anyway.






Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"