Right now it appears that it is truncating it at 8000.



Is this what you are referring to:

Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.


Can you clarify Fabio? I'm not sure I understand what you wrote. What extra characters will be lost using cast()?

This appears to work:

select * from mymain where my_id = '01047'
set concat_null_yields_null off
update mymain set notes = cast(notes as varchar) + 'and Test...' WHERE my_id = '01047'
select * from mymain where my_id = '01047'
set concat_null_yields_null on

After running the UPDATE line, 'and Test...' is appended to the end of the field.

Would convert work better? I thought they had similar functionality...



>>Someone in the office figured out that CAST() works...
>>
>
>of course. CAST declare that you accept the loss of extra characters.
>
>>http://msdn2.microsoft.com/en-us/library/ms187928.aspx
>>
>>
>
>>>Well, Chad that works great! However, I just checked the second table and it is text not char so it doesn't work on that. What would work with a text field type in sql server?
>>>
>>>
>>>
>>>
>>>
>>>>Tracy,
>>>>
>>>>Just RTRIM() the name inside the ISNULL().
>>>>
>>>>HTH,
>>>>Chad
>>>>
>>>>
>>>>>update tbl_main set name = isnull(name,'') +' MORE STUFF TO ADD TO NAME FIELD' where my_id = '01-011'
>>>>>
>>>>>
>>>>>Why not? It returns string or binary data would be truncated.
>>>>>
>>>>>Cannot do this either because it is a char:
>>>>>
>>>>>update tbl_main set name .write (N' MORE STUFF',NULL,0) where my_id = '01-011'