Cannot delete rows: the text, ntext, and image...

• Troy Scheffel

  SSC Enthusiast

  Points: 196

  More actions

  March 18, 2008 at 10:02 am

  #188320

  I have a MSSQL2000 table with the following schema:

  PlanCodeID intUnchecked

  PlanTier intUnchecked

  PlanContentSectionIDintUnchecked

  HtmlContentvarchar(7000)Checked

  Unchecked

  When I open the table in MSSQL2005 SSMS and attempt to delete a few rows, I receive the following popup error regarding a specific row:

  "No rows were deleted.

  A problem occurred attempting to delete row 7.

  Error Source: .Net SqlClient Data Provider.

  Error Message: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

  Correct the errors and attempt to delete the row again or press ESC to cancel the change(s)."

  Row 7 contains:

  110123[ about 5K HTML chars here ]

  I *CAN * delete the row via the following query:

  delete from plancontent

  where plancodeid = 1101 and plantier = 2 and plancontentsectionid = 3

  I can also change the varchar field contents via the Open Table window, e.g. replacing existing contents with "abc". I just can't delete that row by highlighting the row and pressing Delete.

  I'm at a loss to explain why this error occurs, especially since I have no text, ntext or image fields in the table. If anyone has pearls of wisdom they're willing to share, I'd be most appreciative. Danke.

  Troy

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  March 18, 2008 at 3:38 pm

  #791166

  does the table have a declared primary key? if not, the interface will attempt to identify the row by qualifying all values. the resulting statement would be something like this:

  delete table where PlanCodeID = {highlighted row's value}

  and PlanTier = {highlighted row's value}

  and PlanContentSectionID = {highlighted row's value}

  and HtmlContent = {highlighted row's value}

  the comparison to HtmlContent produces your error:

  Error Message: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

• Troy Scheffel

  SSC Enthusiast

  Points: 196

  More actions

  March 18, 2008 at 7:36 pm

  #791238

  Oops, I forgot to mention that, sorry. The three int fields together form a composite primary key.

  I've assumed it is merely some kind of limitation of the MSSQL Open Table user interface since I can execute the query with success.