>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
>

>

With this you don't fix anything. I spoke of to CAST( AS VARCHAR(N))

create TABLE #table (notes varchar(10),notes2 varchar(10))
GO

INSERT INTO #table VALUES (REPLICATE('B',10),NULL)
GO

UPDATE #table SET notes2 = notes + 'A'	-- warning
GO

UPDATE #table SET notes2 = CAST(notes AS VARCHAR) + 'A'	-- warning too !!!
GO

DROP TABLE #table 
go

You have to uses ISNULL() with attention because for me exists a bug and it can return not expected datatype.

try

update tbl_main set name = COALESCE(name,'') +' MORE STUFF TO ADD TO NAME FIELD' where my_id = '01-011'