Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete statement not delting rows? Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:35 AM
Points: 5, Visits: 55
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
Post #1597377
Posted Wednesday, July 30, 2014 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:32 PM
Points: 7,152, Visits: 15,633
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?
Post #1597753
Posted Wednesday, July 30, 2014 8:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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
Post #1597758
Posted Wednesday, July 30, 2014 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:35 AM
Points: 5, Visits: 55
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.
Post #1597771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse