Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Can I check if refresh is already running on an object?

Hello,

I have multiple schedule jobs running that will run SF_Refresh on the same Salesforce object. Sometimes they step on each other where Job A is in the processing of running SF_Refresh @table_name = 'Opportunity', then Job B starts and executes SF_Refresh @table_name = 'Opportunity'.

I'm looking to prevent this and setup of something in Job B that checks if a SF_Refresh @table_name = 'Opportunity' is already running. And if so, just wait for it to finish.

So I'm hoping there is a mechanism where I can check this. Would you be able to advise if something like this exists?

Thanks,
Sean
1 person has
this question
+1
Reply
  • May I ask why you have multiple jobs running a refresh on the same object at the same time? We recommend not doing this at all as they could run into each other and pollute the local table.

    DBAmp does not have a way to check if there is an SF_Refresh running on a table at a certain time.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hello,

    Its a little complicated, but basically I have 2 jobs on completely different schedules which need up-to-the-minute info about the Opportunity object. And so at times, the jobs will start at the exact same time resulting in this problem.

    The only thing I could come up with is interrogating the TableRefreshTime table that gets created/updated to figure out when the Opportunity object was last updated, but it doesn't fully solve my problem since the table is only being updated AFTER the refresh has been completed.

    What I was considering is adding another column to the TableRefreshTime table. "Status" or something like that which would update to "Pending" as the very first thing SF_Refresh does and "Complete" at the very end. This would pretty much do what I want.

    I don't want to modify the base SF_Refresh proc that comes with your product, I'm sure that's not advised...But what about creating a wrapper proc of my own to accomplish this. Something like:

    CREATE PROC SF_Refresh_With_Status /*params...*/
    BEGIN
    UPDATE TableRefreshTime SET Status = 'Pending' WHERE TblName = @SFObject
    EXEC SF_Refresh /*params...*/
    UPDATE TableRefreshTime SET Status = 'Complete' WHERE TblName = @SFObject
    END

    Would this be okay, or am I still treading into dangerous waters?

    Thanks,
    Sean
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • In thinking about this more, SF_Refresh is built and handles running multiple jobs at the same time on the same table. Unless you are seeing any errors, then there should not be a problem.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I have been. On occasion, not all the time.

    Maybe we are just on too old of a version? We are running 3.5.2.0.

    Assuming I can't upgrade, does the potential solution above sound like a viable alternative?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • What are the errors you see? Do you have complete message output showing the errors you see on occasion?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • In one day we had 3 failures. Here is the output in our job history:

    --This one happened once.
    Executed as user: [Removed]. --- Starting SF_Refresh for Opportunity V.20.7
    [SQLSTATE 01000] (Message 0) 18:15:00: Using Schema Error Action of yes
    [SQLSTATE 01000] (Message 0) 18:15:01: Using last run time of 2018-11-15 17:42:00
    [SQLSTATE 01000] (Message 0) 18:15:02: Identified 4 updated/inserted rows.
    [SQLSTATE 01000] (Message 0) 18:15:02: Identified 0 deleted rows.
    [SQLSTATE 01000] (Message 0) 18:15:02: Adding updated/inserted rows into Opportunity
    [SQLSTATE 01000] (Message 0) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    [SQLSTATE 42000] (Error 3930) Failure in dbo.uspExecuteProcessGroup for ProcessGroup 15min
    [SQLSTATE 42000] (Error 50000) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    [SQLSTATE 25000] (Error 266) Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    [SQLSTATE 25000] (Error 266) Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
    [SQLSTATE 42000] (Error 3998). The step failed.

    --This one happened twice.
    Executed as user: [Removed]. --- Starting SF_Refresh for Opportunity V.20.7
    [SQLSTATE 01000] (Message 0) 16:45:01: Using Schema Error Action of yes
    [SQLSTATE 01000] (Message 0) 16:45:01: Using last run time of 2018-11-15 16:12:00
    [SQLSTATE 01000] (Message 0) 16:45:02: Error when creating delta table. Schema changed after the target table was created. Rerun the Select Into query.
    [SQLSTATE 01000] (Message 0) --- Ending SF_Refresh. Operation FAILED.
    [SQLSTATE 01000] (Message 0) Mail (Id: 78459) queued.
    [SQLSTATE 01000] (Message 0) Failure in dbo.uspExecuteProcessGroup for ProcessGroup 15min
    [SQLSTATE 42000] (Error 50000). The step failed.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • What version of DBAmp do you have installed?

    Have you modified the Sf_refresh proc?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Based on the message below, it appears the DBAmp stored procedures were not upgraded at the last upgrade. Please run the create DBAmp sprocs.sql to update the stored procedures.

    SF_Refresh for Opportunity V.20.7
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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