Concatenating columns with datatype ntext

How can I concatenate 2 columns, both of type ntext, in a select statement?
I get the following error.
"Invalid operator for datatype. Operator equals add, type equals ntext"You cannot use concatenation on TEXT datatype. If the length of the value is
less than 8000, you can use the CAST function to change it to VARCHAR
datatype and do the concatenation. Otherwise, you'll have to return them as
distinct values to the client and leverage the client's string concatenation
capabilities.
Anith|||Thanks, Anith, for your reply.
Actually I was trying some data transfer between 2 databases, so the client
option is not feasible for me and length of some column values is more than
a
varchar would permit, so that's not an option either. But knowing it is not
possible is good enough, so I can think of something else.
"Anith Sen" wrote:

> You cannot use concatenation on TEXT datatype. If the length of the value
is
> less than 8000, you can use the CAST function to change it to VARCHAR
> datatype and do the concatenation. Otherwise, you'll have to return them a
s
> distinct values to the client and leverage the client's string concatenati
on
> capabilities.
> --
> Anith
>
>|||You might also want to look at the UPDATETEXT function, but you'll have to
massage each row at a time. The general workaround is to get multiple
VARCHAR columns back like:
SELECT SUBSTRING( textcol, ( 0 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 1 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 2 * 8000 ) + 1, 8000 ),
...
Also in SQL 2005 you will have character types with much better
capabilities.
Anith