Invalid operator for data type. Operator equals add, type equals text.

Today I got the below error when I tried to concatenate two columns of VarChar datatype. 


Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type.
Operator equals add, type equals text.


Example


I have a table called StudentComments with fields StudentID, CommentsOnTeacher, CommentsOnSubject. If we are running the following select statement will get error


SELECT [StudentID], [CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments] FROM [dbo].[StudentComments]


To work around this error you need to CAST the VarChar column into VarChar first before concatenating the columns. The following query will avoid this error:


SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VarChar(8000)) +
CAST([CommentsOnSubject] AS VarChar(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]


The only drawback with this work around is that you are limiting both TEXT columns to 8000 characters each. One other way to work around this is to do the concatenation in the client application instead of having SQL Server do the concatenation.