SQL Server Helper

Tip of the Day : How to Read and Load an Excel 2007 or Excel 2010 File Without Using Import/Export Utility

Error Messages

Home > SQL Server Error Messages > Msg 403 - Invalid operator for data type. Operator equals add, type equals text.

SQL Server Error Messages - Msg 403 - Invalid operator for data type. Operator equals add, type equals text.

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.