Data types text and varchar are incompatible in the equal to operator

mgrantom's profile image mgrantom posted 3 years ago in General Permalink

I am a a rookie to SQL Server but I have been struggling to learn it for a long time. I created a View from 3 joined Tables. When I try to edit a field of type varchar, which holds a date, I get SQL Error 402 "The data types text and varchar are incompatible in the equal to operator. I only want to paste in a bunch of correct dates. I cannot modify the structure of the underlying tables since they are part of a TimeMatters legal database that I have been using for well over 20 years and I cannot afford to break that system. Any help would be appreciated.

ansgar's profile image ansgar posted 3 years ago Permalink

It would help to see the UPDATE query which HeidiSQL tries to run after your modification. Please paste it here. It should be visible in the lower log panel.

jungti1234's profile image jungti1234 posted 3 years ago Permalink

UPDATE TOP(1) [table] SET "HASHTAG"='[시즌14] 3차 팝퀴즈 - 무림페이퍼 편' WHERE "LGTL_CD"='something' AND "USER_ID"='something' AND "POST_TP"='0' AND "ISCD" IS NULL AND "LG_CD"='8067' AND "MEMO_TEXT"='<p style=" TEXT-align: center;">some html</p>' AND "IMAGE_NM"='3' AND "LINK_URL"='v3' AND "HASHTAG"='something' AND "POST_DATE"='20210601' AND "POST_TIME"='085340' AND "LIKE_CNT"=4 AND "WARN_CNT"=0 AND "SUB_USER_ID"='something' AND "HATE_CNT"=0 AND "SAVE_DATETIME"='20210601 08:53:40:817' AND "HIT_CNT"=373;

/ SQL 오류 (402): The data types text and varchar are incompatible in the equal to operator. /

This problem has been around for a long time. I don't know it must needs be following all of column/value on where condition. (is it due to there no pk at table?)

mgrantom's profile image mgrantom posted 3 years ago Permalink

It would help to see the UPDATE query which HeidiSQL tries to run after your modification. Please paste it here. It should be visible in the lower log panel. Here is what HeidiSQL tries to run: UPDATE TOP(1) "TimeMatters"."dbo"."2021HCADCases" SET "ProDate"='5/13/2021 ' WHERE "AccNum2"='Redacted' AND "mat_ref"='Redacted' AND "ccode"='TXPR' AND "mat_no"='Redacted' AND "client"='Redacted' AND "caption"='Redacted' AND "memo"='Redacted' AND "mat1_02_01"='Redacted' AND "Employer"='' AND "Client_Address"='Redacted' AND "Client_Address2"='' AND "Client_City"='Redacted' AND "Client_State"='TX' AND "Client_Zip"='Redacted' AND "Client_Tel"=' ' AND "ClntCell"='Redacted' AND "PropID"=' / SQL Error (402): The data types text and varchar are incompatible in the equal to operator. /

Thanks for the replies, my understanding is that there is no full text index on the table/view. If I create a full text index on the tables involved would this possibly break my underlying system?

Please login to leave a reply, or register at first.