SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete statement not delting rows?


Delete statement not delting rows?

Author
Message
adrian-768020
adrian-768020
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 85
Has anyone come across a situation where a delete statement doesn't give an error, but also doesn't delete the rows specified?

Yes the rows exist as outside the scope of the running application, we can run the same SQL and it does delete the expected rows.

  • It's sql server 2005 sp4

  • It's an ad-hoc delete, i.e. not inside a transaction.

  • The table is accessed via a View which just cuts down the result set on one column's contents.

  • The underlying table has several indexes, several foreign key parents and 2 foreign key children (both children are on delete cascade).

  • There are no triggers on the table.

  • The client app is connecting via odbc.


A coleague rememebers something like this in sql 2000 where deletes on tables with foreign keys had problems building an execution plan, and that just failed silently, but I can't find any details of that.

Anyone?

Thanks,
-Ade
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12723 Visits: 18584
Do you know for a fact that the delete operation finishes? With an ODBC connection, you would be subject to connection timeouts, which as I recall would rollback the operation.

Also - are you testing the delete operation using the same user/security as what the application would be using?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33388 Visits: 18560
In addition to what Matt has said, you could load up a server side trace to try and capture better visibility into what is happening.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

adrian-768020
adrian-768020
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 85
Timeouts are caught, and we're not getting any of those. Tracing isn't recording any errors or warnings, so as far as we can tell, the Delete is being done, it's not timing out, but it just isn't deleting the rows. it's not a collation issue nor is it any odd chars in the data, they're just numers.

The only oddity we've found out is that they're running the 2005 DB in 2000 compatibility mode.

We don't have any access to the server, we can only ask them to do things for us.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search