Append to end of text data type field in sql 2000

17 Aug 2006 1:17 PM

TCH

The notes field is type text in sql 2000. I need to append a line of text
to it. This truncates at 8000:

update tbl_main set notes = cast(notes as varchar) + ' This line is appended
to the text field.' WHERE rcd_id = '01047'

17 Aug 2006 1:33 PM

Baz

"TCH" <program***@hotmail.com> wrote in message
news:%23qqTy9fwGHA.4612@TK2MSFTNGP02.phx.gbl...
> The notes field is type text in sql 2000. I need to append a line of text
> to it. This truncates at 8000:
>
> update tbl_main set notes = cast(notes as varchar) + ' This line is
appended
> to the text field.' WHERE rcd_id = '01047'
>
>

You are concatenating varchar, result varchar. Max length of varchar is
8000.

To update text columns, look up UPDATETEXT in Books Online.

17 Aug 2006 1:35 PM

Aaron Bertrand [SQL Server MVP]

Take a look at READTEXT, WRITETEXT, UPDATETEXT in Books Online. You can't
append to a TEXT column this way.

In SQL Server 2005, you will be able to use VARCHAR(MAX) which is a first
class citizen (e.g. does not have all of the limitations of TEXT).

Show quote

"TCH" <program***@hotmail.com> wrote in message
news:%23qqTy9fwGHA.4612@TK2MSFTNGP02.phx.gbl...
> The notes field is type text in sql 2000. I need to append a line of text
> to it. This truncates at 8000:
>
> update tbl_main set notes = cast(notes as varchar) + ' This line is
> appended to the text field.' WHERE rcd_id = '01047'
>

17 Aug 2006 2:58 PM

Madhivanan

http://www.nigelrivett.net/SQLTsql/InsertTextData.html

Madhivanan

Aaron Bertrand [SQL Server MVP] wrote:
Show quote

> Take a look at READTEXT, WRITETEXT, UPDATETEXT in Books Online. You can't
> append to a TEXT column this way.
>
> In SQL Server 2005, you will be able to use VARCHAR(MAX) which is a first
> class citizen (e.g. does not have all of the limitations of TEXT).
>
>
>
>
>
> "TCH" <program***@hotmail.com> wrote in message
> news:%23qqTy9fwGHA.4612@TK2MSFTNGP02.phx.gbl...
> > The notes field is type text in sql 2000. I need to append a line of text
> > to it. This truncates at 8000:
> >
> > update tbl_main set notes = cast(notes as varchar) + ' This line is
> > appended to the text field.' WHERE rcd_id = '01047'
> >