Help get this topic noticed by sharing it on Twitter, Facebook, or email.
I’m frustrated

Failure when deleting from a specific table

I'm setting up a process on to purge old records from some history tables in Salesforce. It works fine for 28 of 29 tables, but one table will not allow deletes.

DELETE OPENQUERY(SALESFORCE, 'SELECT Id FROM {table} WHERE CreatedDate < YYYY-MM-DDT00:00:00Z')

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7345, Level 16, State 1, Line 1
The OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" could not delete from table "SELECT Id FROM {table} WHERE CreatedDate < 2013-03-05T00:00:00Z". Unknown provider error.

The DELETE fails for this table for the four-part-name form also, with the same messages.
The table is being copied by ReplicateAll with no problems, and I can run SELECT and UPDATE queries on it (with the same WHERE clause) with no problems.

We cannot identify any permission differences with this table compared to the others. There are no triggers or dependencies on any of these tables that could be preventing deletion, and we can delete single records in the table through the Salesforce GUI with no problems. The records can also be deleted by SF_BulkOps with no problems.

Any ideas on why DBAmp cannot delete from one specific table?
1 person has
this problem
+1
Reply
  • 1. The salesforce api does not allow you to delete history records directly. You must delete the parent of the history record instead.

    2. My recommendation is that you not use SQL Delete statements for your other mass deletes because you will run up your api calls and get poor performance. The lack of performance is due to the fact that the deletes are sent to salesforce 1 at time.

    A much better pattern is to use sf_bulkops:

    For example, if your table is name CustObj__c:

    Select Id, cast('' as nvarchar(255)) as Error
    into CustObj__c_dels
    from salesforce...CustObj__c
    where CreatedDate < ....

    exec sf_bulkops 'Delete','SALESFORCE','CustObj__c_dels'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 1. These tables are custom objects that are filled by triggers, Salesforce is not aware that they are history tables.

    2. I may end up using sf_bulkops, but I have some quibbles with your Error definition. I use Error = CAST(SPACE(75) AS NVARCHAR(2000)). SPACE(75) will preallocate space to hold the values so you don't have a lot of page splits when sf_bulkops starts to populate it. I have seen (very rarely) error messages longer than 255 chars, NVARCHAR(2000) avoids having them truncated.

    It's hard to argue with the logic that DELETE is the wrong approach because of the API issues. I am confusing the extra work for me to write all those lines instead of a one-line DELETE, with the extra work done by the one-line solution every time it runs. I would still like to know why DBAmp is picking on this one table though, if just to satisfy my curiosity.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 1. What version of DBAmp are you running ? They may help diagnose the reason for the error on the table ?

    2. Good tip. Also, it is not just the api calls that makes sf_bulkops better. It is the error handling. Since the salesforce api is non-transactional, if one row fails in a batch of 100 , the other 99 succeed. Because this is not true of a SQL DELETE on a normal local table, the SQL DELETE construct does not have a good way to communicate back row-by-row errors. The sf_bulkops does this with the Error column.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • My version is:
    DBAmp Bulk Operations. V2.15.2 (c) Copyright 2006-2012 forceAmp.com LLC
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Fixed bug:

    Version 2.15.6
    =================================
    - Fixed bug with long IN clauses and other complicated queries
    - Fixed bug with SQL Delete statement failing with catastrophic error

    Upgrade instructions at http://www.forceamp.com/upgrade.htm
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I upgraded to version 2.16.8 and don't see the DELETE error anymore. (I also changed some of the code to use sf_bulkops, so I'm not sure which action is responsible.)

    Now I have an issue with document attachments. I had to restore the registry settings for MaxBase64Size and BitBoolean, in spite of the note for version 2.15.1 that says registry settings will survive an upgrade. Now I can see the binary Documents.Body field using a four-part-name reference, or in a local table populated by SF_ReplicateAll, but it is NULL when read by OPENQUERY. Have I overlooked a configuration setting, or is this a new bug?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated