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

how do I know if SF_Tableloader is using soap or bulkapi with an UPDATE?I am updating 2 columns on the LEAD table.

This is what my UPDATE table looks like

CREATE TABLE [dbo].[Lead_MatchedUpdate](
[Id] [nchar](18) NULL,
[EMPID__c] [varchar](50) NULL,
[Name_Match_Status__c] [varchar](255) NULL
) ON [PRIMARY]

GO
1 person has
this question
+1
Reply
  • What version of DBAmp are you running? It depends on how many rows are in the input table. If there are more than 5,000 rows it used the BulkAPI, if there are less it uses the SOAP API.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Error error message says DBAmpNet 2.3.7.4.0

    We had 689 records.

    The first 400 records showed as successful in the RESULT table.

    We re-ran the job and it completed successfully.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • What was the error message? Do you have the output we can look at?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • We had the issue occur yesterday and today. It's fine with a dozen records, but both times it failed it was with about 600 records. It processes the frist 400 then fails on the second set.

    --- Starting SF_TableLoader for Lead_MatchedUpdate V3.7.4
    10:14:43: Run the DBAmpNet2.exe program.
    10:14:43: DBAmpNet2 3.7.4.0 (c) Copyright 2015-2017 forceAmp.com LLC
    10:14:43: Parameters: update Lead_MatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD
    10:14:43: Using the Salesforce SOAP API.
    10:14:44: Drop Lead_MatchedUpdate_Result if it exists.
    10:14:44: Create Lead_MatchedUpdate_Result with new structure.
    10:14:44: Add Error column in Lead_MatchedUpdate_Result.
    10:14:46: Error: Unable to make changes to Salesforce object.
    10:14:46: System.Net.WebException: The request failed with HTTP status 400: No URI.
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects)
    at DBAmpNet2.LoaderSOAP.LoadRecords()
    10:14:47: Error: Unable to make changes to Salesforce object.
    10:14:47: System.Net.WebException: The request failed with HTTP status 400: No URI.
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects)
    at DBAmpNet2.LoaderSOAP.LoadRecords()
    10:14:47: 657 rows read from SQL Table.
    10:14:47: 257 rows failed. See Error column of row for more information.
    10:14:47: 400 rows successfully processed.
    10:14:47: Errors occurred. See Error column of row for more information.
    10:14:47: DBAmpNet2 Operation FAILED.
    10:14:49: Percent Failed = 39.100.
    10:14:49: Error: DBAmpNet2.exe was unsuccessful.
    10:14:49: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsSoap "Update" "Lead_MatchedUpdate" "TENHDCTHCRMSQL1" "salesforce backups" "SALESFORCE_PROD" " "
    --- Ending SF_TableLoader. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_TableLoader, Line 338
    SF_TableLoader Error: 10:14:43: DBAmpNet2 3.7.4.0 (c) Copyright 2015-2017 forceAmp.com LLC10:14:43: Parameters: update Lead_MatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD 10:14:43: Using the Salesforce SOAP API.10:14:44: Drop Lead_MatchedUpdate_Result if it exists.10:14:44: Create Lead_MatchedUpdate_Result with new structure.10:14:44: Add Error column in Lead_MatchedUpdate_Result.10:14:46: Error: Unable to make changes to Salesforce object.10:14:46: System.Net.WebException: The request failed with HTTP status 400: No URI. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects) at DBAmpNet2.LoaderSOAP.LoadRecords()10:14:47: Error: Unable to make changes to Salesforce object.10:14:47: System.Net.WebException: The request failed with HTTP status 400: No URI. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects) at DBAmpNet2.LoaderSOAP.LoadRecords()10:14:47: 657 rows read from SQL Table.10:14:47: 257 rows failed. See Error column of row for more information.10:14:47: 400 rows successfully processed.10:14:47: Errors occurred. See Error column of row for more information.10:14:47: DBAmpNet2 Operation FAILED.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Does this error occur every time you run SF_TableLoader with this Lead input table? Also, does this error happen when running SF_TableLoader with any other input table other than the one above?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • The LEAD table is the only table we update.

    I do not get the error all the time.

    In development I never encountered the error and we pushed several thousand records.

    We went to production yesterday, hitting SaleForce production, and the first several runs were with less than 50 records and all was well.

    The first time we sent 689 records it failed after the first set of 400.

    Then overnight it was fine with a couple of records being sent.

    Then this morning it tried to do over 600 and failed again. I manually reran it and it failed all records, then I manually ran again and it process 400, then I manually ran again and it did all records. the manual runs are the SQL Server Management Studio. The batch jobs are from from a SSIS package that calls the same TSQL code.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can you run the following select query from the _Result table and see if there is anything in the error columns for the ones that failed:

    select Error from Lead_MatchedUpdate_Result where Error not like '%Operation Successful%'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Once we re-ran the job the table was cleared out.

    From what I recall, it was a similar message to what I saw when I ran it manually:
    "HTTP status 400: No URI."

    it appears to be some sort of network issue, but it's odd that it only happens when we do sets of data > 600 records in prod.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I am still getting getting the Error almost daily when I process more than a couple hundred records in production.

    I don't know if DBAmp is sending the records too fast.

    Is there anywhere else I can check to determine the cause of the error?

    Date 4/5/2019 8:01:48 AM
    Log Job History (SSIS: Match Leads PROD)

    Step ID 2
    Server TENHDCTHCRMSQL1
    Job Name SSIS: Match Leads PROD
    Step Name Re-run push to SalesForce
    Duration 00:00:42
    Sql Severity 16
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: NT SERVICE\SQLSERVERAGENT. ...TE 01000] (Message 0) 08:01:49: Run the DBAmpNet2.exe program. [SQLSTATE 01000] (Message 0) 08:01:49: DBAmpNet2 3.7.4.0 (c) Copyright 2015-2017 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 08:01:49: Parameters: update Lead_MatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD [SQLSTATE 01000] (Message 0) 08:01:49: Using the Salesforce SOAP API. [SQLSTATE 01000] (Message 0) 08:01:50: Drop Lead_MatchedUpdate_Result if it exists. [SQLSTATE 01000] (Message 0) 08:01:50: Create Lead_MatchedUpdate_Result with new structure. [SQLSTATE 01000] (Message 0) 08:01:50: Add Error column in Lead_MatchedUpdate_Result. [SQLSTATE 01000] (Message 0) 08:01:51: Error: Unable to make changes to Salesforce object. [SQLSTATE 01000] (Message 0) 08:01:51: System.Net.WebException: The request failed with HTTP status 400: No URI. [SQLSTATE 01000] (Message 0) at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) [SQLSTATE 01000] (Message 0) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) [SQLSTATE 01000] (Message 0) at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects) [SQLSTATE 01000] (Message 0) at DBAmpNet2.LoaderSOAP.LoadRecords() [SQLSTATE 01000] (Message 0) 08:01:53: 657 rows read from SQL Table. [SQLSTATE 01000] (Message 0) 08:01:53: 200 rows failed. See Error column of row for more information. [SQLSTATE 01000] (Message 0) 08:01:53: 457 rows successfully processed. [SQLSTATE 01000] (Message 0) 08:01:53: Errors occurred. See Error column of row for more information. [SQLSTATE 01000] (Message 0) 08:01:53: DBAmpNet2 Operation FAILED. [SQLSTATE 01000] (Message 0) 08:01:55: Percent Failed = 30.400. [SQLSTATE 01000] (Message 0) 08:01:55: Error: DBAmpNet2.exe was unsuccessful. [SQLSTATE 01000] (Message 0) 08:01:55: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe BulkOpsSoap "Update" "Lead_MatchedUpdate" "TENHDCTHCRMSQL1" "salesforce backups" "SALESFORCE_PROD" " " [SQLSTATE 01000] (Message 0) --- Ending SF_TableLoader. Operation FAILED. [SQLSTATE 01000] (Message 0) SF_TableLoader Error: 08:01:49: DBAmpNet2 3.7.4.0 (c) Copyright 2015-2017 forceAmp.com LLC08:01:49: Parameters: update Lead_MatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD 08:01:49: Using the Salesforce SOAP API.08:01:50: Drop Lead_MatchedUpdate_Result if it exists.08:01:50: Create Lead_MatchedUpdate_Result with new structure.08:01:50: Add Error column in Lead_MatchedUpdate_Result.08:01:51: Error: Unable to make changes to Salesforce object.08:01:51: System.Net.WebException: The request failed with HTTP status 400: No URI. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at DBAmpNet2.sforce.SforceService.update(sObject[] sObjects) at DBAmpNet2.LoaderSOAP.LoadRecords()08:01:53: 657 rows read from SQL Table.08:01:53: 200 rows failed. See Error column of row for more information.08:01:53: 457 rows successfully processed.08:01:53: Errors occurred. See Error column of row for more information.08:01:53: DBAmpNet2 Operation FAILED. [SQLSTATE 42000] (Error 50000) --- Starting SF_TableLoader for Lead_MatchedUpdate V3.7.4 [SQLSTATE 01000] (Message 0) 08:01:57: Run the DBAmpNet2.exe program. [SQLSTATE 01000] (Message 0) 08:01:57: DBAmpNet2 3.7.4.0 (c) Copyright 2015-2017 forceAmp.com LLC [SQLSTATE 01000] (Message 0) 08:01:57: Parameters: update Lead_MatchedUpdate TENHDCTHCRMSQL1 salesforce backups SALESFORCE_PROD [SQLSTATE 01000] (Message 0) 08:01:57: Using the Salesforce SOAP API. [SQLSTATE 01000] (Message 0) 08:01:58: Drop Lead_MatchedUpdate_Result if it exists. [SQLSTATE 01... The step failed.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • We are still looking into it. You could try upgrading to the latest version of DBAmp, but not sure that will fix your issue. You could try just forcing SF_TableLoader to use the bulkapi and see if it goes away when using the bulkapi:

    exec SF_TableLoader 'Update:bulkapi', 'SALESFORCE_PROD', 'Lead_MatchedUpdate'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I suspect this is a network issue. Have you asked your network team to take a look ?

    Is the production box using the exact same proxy path that the dev box used ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • How do I specify both bulkapi and ignore nulls on the UPDATE parm ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Unfortunately, the salesforce bulkapi does not allow you to specify ignoreNulls.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I am still having issues with SF_TableLoader throwing errors when updating the LEAD table when processing more than 200 records.

    I changed it to bulkapi.

    There are 1222 records to process. It failed the first time, then I looped to have it try again and the 3rd attempt it made it.

    Does anything in the attached file point to an issue?

    what is the latest version of DBAmp?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Would you be able to join a web meeting now or later this afternoon (2pm CST) so we can take a look?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can we meet a little earlier, or Thursday AM?

    The process runs on a remote server and I need to VPN in to the servers. When I VPN in to the servers most internet access is blocked (including this support site).

    Is there anything I can look at ahead of tie to get print screens?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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

  • This reply was removed on 2019-04-11.
    see the change log
  • Can you send an email to support at forceamp.com (support@forceamp.com) so we can send you the goto meeting invite for 930 this morning?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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