January 19, 2012

No rows were deleted

Error:
- A SQL Server Database contains some tables but one of table's rows cannot be deleted!!
- How i can delete some NULL rows in my table ?
- Can't delete rows (...) in Microsoft Visual Studio ...

Error Message:
No rows were deleted.
A problem occured attempting to delete row 3.
Error Source: Microsft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make
the row unique or they alter multiple rows (3 rows)
Correct the errors and attempt to delete rows again or press ESC to cancel the change(s)
Problem Causes:
This issue occurs if the following conditions are true:
- The table contains one or more columns of the text or ntext data type.
- The value of one of these columns contains the following characters:
      - Percent sign (%)
      - Underscore (_)
      - Left bracket ([)
- The table does not contain a primary key.

Solution:
This is a Microsoft BUG (BUG-925719)
There's not any solution. You need to have a new table with a primary key or correct values and copy data into new table and drop old table, Sorry.

Solution In Microsoft Visual Studio (NEW):
- Goto Server Explorer
- Goto Database > Tables > The Table
- RightClick and Select Edit Table Schema
- ADD a new Column with these options
        - Data Type: uniqueidentifier
        - Allow Nulls: No
        - Unique: Yes
        - Primary Key: Yes
        - Is RowGuid: True
- Save and Close and open Table and delete rows ;-)

REF: http://amastaneh.blogspot.com/2012/01/no-row-was-updated.html

6 comments:

deadlyStUd said...

thankss a lott dude!! this really worked!! had a headace by this error!!!

salah saleh said...

thnx dude.....grt work

salah saleh said...

grt work dude....it really work

Johanna Rodriguez said...

También para corregir éste tipo de error, es cuestión de Agregar la llave primaria (sí no la tiene), en la parte de diseño de la tabla guardar los cambios de la tabla, si no les permite se van a Herramientas - Opciones - Diseñador - Quitan la opción Prevenir guardar cambios que sean requeridos la re-creación de la tabla.

Con ésto he solucionado mi problema y pude eliminar las filas, una ve agregué ésta columna como llave primaria.

Gracias por este pos, me ha servido de guía!

JP said...

Try this (change everything that begins with a # for your own columns/tables) ...
Be careful on the columns you pick
It will delete all duplicates :

;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY #Col1, #Col2, #Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1

Anonymous said...

Thanks, this worked perfectly!