SQL Server Error Messages - Msg 403¶

Error Message¶

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

Causes:¶

This error occurs when you are trying to concatenate 2 columns of TEXT data type. To illustrate, assume that you have the following table definition:

CREATE TABLE [dbo].[StudentComments] (
    [StudentID]            INT,
    [CommentsOnTeacher]    TEXT,
    [CommentsOnSubject]    TEXT
)

The following SELECT statement will generate the error:

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

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

Solution / Work Around:¶

To work around this error you need to CAST the TEXT 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.