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

Cannot execute the query when querying through view

We have a monitoring job that runs every 10 seconds to check for any new rows in a table.

We can go days without issues then several times in an hour we receive "Cannot execute the query", then it's ok in the next 10 seconds.

We occasionally get an erorr while trying to UPDATE the SF table, but that happens maybe once a month.

We are running v3.7.8

We are running in QA, but also get the issue in PROD less frequently.

This is our error
Cannot execute the query "SELECT "Tbl1005"."Campaign_ID__c" "Col1019","Tbl1005"."Id" "Col1022","Tbl1005"."Request_Type__c" "Col1030" FROM "Campaign_Audience_Request_Tracking__c" "Tbl1005" WHERE "Tbl1005"."Status__c"='Pending'" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".

This is our SELECT
SELECT DISTINCT
sf.Id AS [Campaign_Audience_Request_ID__c],
sf.[Campaign_ID__c],
sf.Request_Type__c,
'Extracted' AS Status
FROM SFViews.dbo.Campaign_Audience_Request_Tracking__c_View sf
where sf.Status__c = 'Pending'
1 person has
this problem
+1
Reply
  • What is the select statement against the SALESFORCE linked server in the underlying Campaign_Audience_Request_Tracking__c_View view? Is that the complete message output that shows up when it errors out or is there more to it?

    When you say you get an error while trying to update the SF table once a month what is the command you are running when that errors out? Do you have complete message output for that showing the error?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • This is the VIEW we are selecting from:
    CREATE VIEW [dbo].[Campaign_Audience_Request_Tracking__c_View] WITH VIEW_METADATA AS SELECT [Campaign_ID__c],[CreatedById],[CreatedDate],[Id],[IsDeleted],[LastModifiedById],[LastModifiedDate],[LastReferencedDate],[LastViewedDate],[Name],[OwnerId],[Request_Type__c],[Status__c],[SystemModstamp] FROM SALESFORCE...[Campaign_Audience_Request_Tracking__c]

    The completeerror message from the SELECT is:
    Cannot execute the query "SELECT "Tbl1005"."Campaign_ID__c" "Col1019","Tbl1005"."Id" "Col1022","Tbl1005"."Request_Type__c" "Col1030" FROM "Campaign_Audience_Request_Tracking__c" "Tbl1005" WHERE "Tbl1005"."Status__c"='Pending'" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".

    This is the stored proc we call to UPDATE the SF table.
    CREATE PROCEDURE [dbo].[SF_UpdateCampaign_Audience_Request_Tracking__c]
    @id NVARCHAR(18),
    @field NVARCHAR(50),
    @value NVARCHAR(2000),
    @p_DBAMP_DB_IN NVARCHAR(50)
    AS
    -- Parameters: @id - id of Campaign_Audience_Request_Tracking__c record to update
    -- @field - field name to update
    -- @value - new value for field

    DECLARE @RecordCount BIGINT
    DECLARE @stmt NVARCHAR(4000)
    SET @stmt = 'update openquery(' + @p_DBAMP_DB_IN + ',''Select Id,'
    SET @stmt = @stmt + @field + ' from Campaign_Audience_Request_Tracking__c where Id='
    SET @stmt = @stmt + '''''' + @id + ''''' '') set ' + @field + ' = ' +@value
    -- print @stmt
    EXEC (@stmt)
    SET @RecordCount = @@ROWCOUNT
    --PRINT @RecordCount
    IF (@@ERROR <> 0)
    RETURN -1
    ELSE
    RETURN @RecordCount

    This is the call to the stored proc to perform the UPDATE where @p_DBAMP_DB_IN = ‘SALESFORCE’
    GO

    EXEC @ReturnCode = SF_UpdateCampaign_Audience_Request_Tracking__c @id,
    'Status__c',
    '''Complete''',
    @p_DBAMP_DB_IN;

    The error message is
    The OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" could not UPDATE table "[DBAmp.DBAmp]".

    Is this a case of losing connectivity to Salesforce?
    Where would i look on the SF side to see if there was an error on the SF side?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • How many records total are in the Campaign_Audience_Request_Tracking__c table?

    As for the update, we highly, highly recommend not updating/inserting using the linked server directly. This is for a couple reasons:

    1. There is no batching, it is 1 API call per 1 record updated/inserted
    2. Performance is not as good the more records you want to update/insert
    3. There is no error handling, there is no way for DBAmp to tell which records were successful and which records failed

    Instead, we recommend you use SF_TableLoader to push the data you want up to Salesforce. Take a look at Chapter 5 of the DBAmp Doc for more information and let us know if you have questions on SF_TableLoader: http://forceamp.com/hats/DBAmpDoc.pdf
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • There are 396 rows in the table. It will grow to 1000 at the most.

    We are updating just 1 record.

    Most days we will do 3-4 updates, but during testing we were doing about 40/day. On a normal prod day we may do 10-20 over 8 hours. Activity is light and not a big API consumer.

    We had used SF_TableLoader but the performance was too slow for the singleton update we were doing, and there was a lot of overhead coding for a 1 row update. We switched to the stored proc and got the code from the DBAMP documentation, chapter 2 .

    When we use the stored proc with SOQL and it fails will there be anyone on the SF side logged for the call?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can you clarify for us, is the SF_UpdateCampaign_Audience_Request_Tracking__c stored procedure and the following select against the view two separate things with two separate errors:

    SELECT DISTINCT
    sf.Id AS [Campaign_Audience_Request_ID__c],
    sf.[Campaign_ID__c],
    sf.Request_Type__c,
    'Extracted' AS Status
    FROM SFViews.dbo.Campaign_Audience_Request_Tracking__c_View sf
    where sf.Status__c = 'Pending'

    Or, is the select against the view inside the SF_UpdateCampaign_Audience_Request_Tracking__c stored procedure and they are the same things with the same errors?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Yes, the UPDATE and SELECT are 2 separate errors. Although I suspect both are related to network issues between SQL Server and SF. I just need to validate that for my SF guys.

    The SELECT runs every 10 seconds so we see more of those errors. It runs through the VIEW.

    The UPDATE only runs when we pick up a record to process. In testing that might be called 40 times a day and over several weeks we only have 1-2 error occurrences. It runs though the stored proc.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Are you running either of these in jobs?

    If you are, can you go to the job steps where these are being run, go to the Advanced Tab, and check the "Log to Table" and "Append output to existing entry in table" options in your Test environment. Then, when you see the errors, go back to the Advanced Tab of the job step and click View and send us the output for review.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I finally got the job to fail again. The job runs every 10 seconds.

    Job 'API- fulfill Campaign requests' : Step 1, 'Fulfill campaign query requests' : Began Executing 2019-07-24 03:27:43

    OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 5103 : FAHttp::Send::WinHttpReceiveResponse Status 503 Service Unavailable". [SQLSTATE 01000]

    Job 'API- fulfill Campaign requests' : Step 1, 'Fulfill campaign query requests' : Began Executing 2019-07-24 03:27:54

    It looks like the call is just timing out
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • The 503 error might be indicating a problem in the Salesforce server with service availability. Are the other jobs connecting to this linked server running correctly?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • We have not had this issue with our other jobs, but the others jobs do run as frequently.

    This job runs every 10 seconds and the other jobs run weekly or monthly.

    I am guessing we are hitting this when SF is performing updates/maintenance on the SF objects, then 10 seconds later we are fine.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I would also check with the network team. There could be a piece of network hardware that is generating that 503 error. Sometimes a web sniffer can throw weird errors when it cannot make it all the way through the incoming packet.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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