x

I cannot delete rows in SQL table

Hello, I have a SQL server database which contains different tables, one of its table's rows cannot be deleted. I don't know what is the problem, I checked table properties and server roles too. I want to mention that some of its rows are used in views.

I try to delete it directly from the SQL server. It gives me the following 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 (5 rows)

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

more ▼

asked Nov 23, 2009 at 07:56 AM in Default

Jahid gravatar image

Jahid
13 1 1 2

Posting the error message you get is a must, really.
Nov 23, 2009 at 08:09 AM Matt Whitfield ♦♦
Do you get referential error?
Nov 23, 2009 at 08:35 AM Peso
(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

It sounds like you're relying on the data tool to handle the deletes but your data has duplicates which is preventing the data tool from completing the operation. I think sp_lock has it nailed, you don't have a primary key on this data.

more ▼

answered Nov 24, 2009 at 09:58 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Have you tried deleting with a manually typed delete statement instead of through the GUI?

The one time I have seen an error like that was when I was deleting rows through the gui that another developer had modified by t-sql command. (Just to be clear, this was setting up test data on a test server. I generally would not recommend using the GUI to make changes in production, especially on a system with many users.)

more ▼

answered Nov 24, 2009 at 02:44 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Are the views created with schemabinding?

more ▼

answered Nov 23, 2009 at 12:05 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

To add to what has already been said and asked....

There are various reasons why you may not be able to delete, or the result of a delete MAY not appear to have worked, including triggers, constraints, permissions, errors etc.

You really need to post both the method by which you are trying to delete (i.e. the SQL) and any resulting error messages or completion messages. This will then help us to help you.

more ▼

answered Nov 23, 2009 at 09:13 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

Are you trying this via SSMS?

Silly question...Does the table have a Primary key?

more ▼

answered Nov 23, 2009 at 08:19 AM

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x52
x14

asked: Nov 23, 2009 at 07:56 AM

Seen: 7473 times

Last Updated: Nov 24, 2009 at 02:29 AM