Post reply

Delete statement not delting rows?

  • July 29, 2014 at 9:57 am

    #150172

    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

  • July 30, 2014 at 8:23 am

    #1733615

    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?

  • July 30, 2014 at 8:27 am

    #1733620

    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[/url]
    Learn Extended Events

  • July 30, 2014 at 8:40 am

    #1733633

    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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply