Saturday, August 22, 2009

Invalid operator for data type. Operator equals add, type equals text.,sql,asp.net jobs,india jobs,

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



I got the above error when i tried to concatenate two columns of Text datatype. I can explain this with the help of an example

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

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


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.

2 comments:

  1. In SQL Server 2005 and above, you can CAST as varchar(MAX) to prevent such truncation. Better yet, change the column datatype from text to varchar(MAX).

    ReplyDelete