Combining multiple text fields into one text field

Question

I'm trying to merge multiple text columns into one concatenated text column. Each of the fields were previously used for various descriptions, but per new reqs, I need all of those fields to be combined into one.

I tried converting them to varchar(max) first then concatenating, but some of the rows have values in these columns which are longer than the max and are being truncated in the result.

Is there a way to combine multiple text fields in SQL Server 2000?

Answer 1

The best advice I have for you is to either

1. perform the concatenation in your middle or presentation tier (or add an abstraction layer that allows this, including routing your query through a newer version of SQL Server which performs the concatenation after pulling through a linked server to 2000); or,
2. upgrade.

You can't fool SQL Server 2000 into supporting [n]varchar(max), and the limitation you've come across is just one of many, many, many reasons the [n]text data types were deprecated.