Server: Msg 306,.....The text, ntext, and image data.......????????????????
-
Comparing between tables, I have found out that some of them result in an error like:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
INSERT INTO table1 SELECT * FROM table1_Temp TempT WHERE NOT EXISTS (SELECT field1 FROM table1 WHERE field1 = TempT.field1)
AnyOne can suggest me how to solve this problem.....(I cannot change the datatype in table fields.....)
Thank in advance.....
-
Can you post the actual query with the table definition? There's a number of things that can be causing this error, which is self explaining btw.
Also you should always specify the column names in the insert.
Insert into dbo.table1 (col1, col2) Select col1, col2 from dbo.Y
If you don't, it's gonna come byte you in the ass when you least expect it.
-
As you already know if your field1 is a text/image type you can not do a straight compare ie. using '='
you can use datalength(field1) to get the size but not very accurate.
-
I have partially solved my problem..
If I convert to varchar the query works.
The problem is that in the case of UPDATE statement, like:
UPDATE tablename SET CAST(field1 as varchar (100)) = CAST(TempT.field1 as varchar (100)),.....From ....
the result is :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
If I write :
UPDATE tablename SET field1 = CAST(TempT.field1 as varchar (100)),.....From ....
the result is the previous error:
Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
So...after the word SET how can I write the convertion with CAST?????
Thank
-
You can't update a text column like this, while I can't show you how I can tell you to go link into the books online for more information.
-
Dude, You seriously need to do some research on datatypes.
Text data type is very difficult to work with, and what your doing will result in data loss.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_0lyd.asp
Also readup on special functions that deal with text datatypes
READTEXT
TEXTPTR
WRITETEXT
UPDATETEXT
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply