locked
Failed to execute the command 'DeleteCommand' for table 'XXX' RRS feed

  • Question

  •  

    I have server db SQL-2005 and local db SQLCE 3.5. I am using SyncServices to synchronize data.

    I have created a project that populates all tables of my local db, and I deleted some records from table 'XXX', then running syncAgent.Synchronize(), its throwing expection with the message 'Failed to execute the command 'DeleteCommand' for table 'XXX''

     

    How can handle this situation, do I have to modify my storedProcs to handle this or do I have to define some custom logic to handle conflicts.

     

    Please through some light.

     

    Thanks,

    • Moved by Max Wang_1983 Thursday, April 21, 2011 11:31 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, November 12, 2007 10:12 PM

Answers

  • I fianlly got to know the problem and resolved. This mgiht be helpful, so I am posting the reply.

     

    In my custom triggers, I always insert a record into my tracking tables(which has not null column as the key of main table) for each change (Update/Delete) instead of updating the existing record with latest change.

     

    When ever I insert+delete+Synchronize() a row, it has nothing to delete from main table of server, but trigger getting fired with null values and trying to insert a row with null values and throwing above exception at server whenever i synchronize.

    So solution is, I changed my trigger to check the PK is null or not null.

    If PK is not null, then only I am inserting a row into my tracking table with Operation as 'D' stands for delete.

     

    -Thanks,

    Friday, November 16, 2007 5:09 PM

All replies

  • Hi Sreevi,

     

    One way to debug this is to try to run your delete command manully on SQL Server management studio. This way you can see that the command you've writting is working as you desire.

     

    Also check the innerException property of the exception you are getting, it could give you more details on what is going wrong.

     

    Thanks

     

    Monday, November 12, 2007 11:37 PM
  • My scenario is like this:

     

    I created empty local sdf, ran Synchronize(), I think local cache enabled and start tracking all changes. Now I Inserted bunch of rows from my application, deleted some of them from my application, then trying to Synchronize() - this time i am getting 'Failed to execute the command 'DeleteCommand' for table 'XXX''

     

    Before deleting rows, i read all the changes into dataset table using GetChanges(), all inserted rows are present in table.

    Deleted rows from table xxx.

    Again I read all the changes into Dataset using GetChanges(), I observed that the deleted rows are not there in Dataset table xxx.

     

    Insert rows + Synchronize() + Delete rows + Synchronize() -- Synchronization is working fine.

    Insert rows + Delete some rows + Synchronize() -- got above exception.

     

    Second option i tried is:

    I modified my delete stored procedure at server to handle this with

    IF Exists(Particular row)

    BEGIN

    Delete Table XXX Where ...

    END

     

    After changing serverside procedure,

    Insert rows + Delete some rows + Synchronize() -- Synchronization worked fine, but there are no records at server.

     

    Looking forward for replies.

     

    -Thanks,

     

     

     

    Tuesday, November 13, 2007 12:17 AM
  • Hi,


    I am not sure I understand everything you said. One thing to note that when you inspect the dataset, deleted rows are there but they are deleted so you need to call RejectChanges() on the data Set to see the deleted rows again.

     

    I've few questions:

    - Now, the rows you inserted then deleted: was that against the server database or client database?

    - Are you able to repro this scenario with any of the samples that I’ve posted at www.syncguru.com. If you haven’t tried, can you please do? I need to repro this and see what is happening. If you cannot repro with the samples, then the sample code might give you clues on what is going wrong with you app.

     

    Thanks

     

    Tuesday, November 13, 2007 6:04 AM
  • I fianlly got to know the problem and resolved. This mgiht be helpful, so I am posting the reply.

     

    In my custom triggers, I always insert a record into my tracking tables(which has not null column as the key of main table) for each change (Update/Delete) instead of updating the existing record with latest change.

     

    When ever I insert+delete+Synchronize() a row, it has nothing to delete from main table of server, but trigger getting fired with null values and trying to insert a row with null values and throwing above exception at server whenever i synchronize.

    So solution is, I changed my trigger to check the PK is null or not null.

    If PK is not null, then only I am inserting a row into my tracking table with Operation as 'D' stands for delete.

     

    -Thanks,

    Friday, November 16, 2007 5:09 PM
  • It maybe an old thread, but this just helped me too!

    I got the same error and did as suggested and checked my triggers - low and behold, I'd mistyped the Tombstone table name in my Delete trigger which was causing a SQL error when trying to delete from the table during a Sync.

    So thankyou Sreevi !
    Tuesday, September 8, 2009 4:24 PM