Msg 402, Level 16, State 1, Line 15 The data types ntext and ntext are incompatible in the equal to operator.

Question

Hi ALL

I'm using

 INSERT INTO LMI_Chat_History
 ([Start Time], [End Time], [Total Time], [Session ID], Name, [Technician Name], [Technician ID],
 [Technician Email], Chatlog, Notes)
 select
 [Start Time], [End Time], [Total Time], [Session ID], Name, [Technician Name], [Technician ID],
 [Technician Email], Chatlog, Notes
 FROM TBL_Temp_LMI_Chat TL
 WHERE NOT EXISTS(
 SELECT  [Start Time], [End Time], [Total Time], [Session ID], Name, [Technician Name], [Technician ID],
 [Technician Email], Chatlog, Notes
 from LMI_Chat_History CL
 WHERE TL.[Start Time]=CL.[Start Time] and TL.[End Time]=CL.[End Time] and TL.[Technician Name]=CL.[Technician Name]
 and TL.[Session ID]=CL.[Session ID] and TL.Name=CL.Name and TL.[Technician Email]=CL.[Technician Email]
 and TL.Chatlog=CL.Chatlog
 )
 

it give sme error

Msg 402, Level 16, State 1, Line 15
The data types ntext and ntext are incompatible in the equal to operator.

Please help me how can i resolve this

Answer 1

You need to use a join for the conditional search in this query, you can't use logical operators on a string-type.

INSERT INTO LMI_Chat_History
 ([Start Time], [End Time], [Total Time], [Session ID], Name, [Technician Name], [Technician ID],
 [Technician Email], Chatlog, Notes)
 SELECT 
 [Start Time], [End Time], [Total Time], [Session ID], Name, [Technician Name], [Technician ID],
 [Technician Email], Chatlog, Notes
 FROM TBL_Temp_LMI_Chat TL
 JOIN LMI_Chat_History CL
 ON TL.[Start Time]+TL.[End Time]+TL.[Technician Name]+TL.[Session ID]+TL.Name+TL.[Technician Email]+TL.Chatlog=
 CL.[Start Time]+CL.[End Time]+CL.[Technician Name]+CL.[Session ID]+CL.Name+CL.[Technician Email]+CL.Chatlog

Something like that.

Answer 2

The text and ntext data types are deprecated, and you would be better off to change the data type of such columns to (n)varchar(MAX). Although, it is not obvious from the column names which columns that would be. Maybe they should be regular (n)varchar?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

I suspect that ChatLog column is ntext. You will need to use

and convert(nvarchar(max), Tl.ChatLog) = convert(nvarchar(max), Cl.Chatlog)

It will be better to change the columns to that type in the table.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog