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

Uploading Attachments to Salesforce that site on a different server than dbAmp

Hello,

First off I have to say I am loving your product!

Now that that's taken care of... I am dealing with something I cant seem to figure out. We recently purchased a couple brand new super fast servers, one web server running 2008 r2 and one is a 2012 for our sql server (dbAmp installed here). They are on separate IP's not on the same network so the 2 talk via datasource and external IP's. When files get uploaded from our apps they sit on the web server. I am trying to upload the image file as attachment to salesforce but since its not on the sql server where dbAmp is loaded I am getting the Unable to read error. All my code is fine since on my dev environment where sql and web are in the same server things upload no problem.

Whats the best way to upload a file from a different server than dbAmp is installed on?

Thanks!
1 person has
this question
+1
Reply
  • DBAmp can upload the binary contents of an attachment 2 ways:

    1. You put the binary contents into a blob column of an Attachment Load table and tell DBAmp sf_bulkops to upload it.

    OR

    2. You put the name of a file in the Attachment Load table and tell DBAmp (while running on the SQL Server box) to open the file and read it. We are running on the SQL box at that moment and really just go to the Windows OS and say "here's a file name, can you open it ?". If there is not a shared drive between the SQL machine and the machine where the file is located, then the call to open the file will fail.

    To really there are to options if you want to upload files that are not accessible from the SQL Server:

    1. Make the file accessible either via a shared drive, etc.

    OR

    2. Before the sf_bulkops call, write your own code to load the binary contents of the attachment into the Attachment_Load table. Then SF_Bulkops is opening the file; it is simply reading the binary column.

    Sorry there is not a cleaner approach right now for files that are not on the SQL machine.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Ok so I set up a mapped network drive on the sql server that points to the location of the image. If I type P: in any window on the sql server it opens the folder of images. All good so far.

    I changed my code to insert P:\image_name as the body in attachment_load tbl

    When I run the app I get the same error unable to read file.

    To test I took the body value from the load table that has the error and pasted it into the location bar of an explorer window on the sql server and boom it opens the image file.

    Shouldn't dbAmp be able to access the file the same way?

    I am stumped. Thanks again for any help.

    I am open to using the blob method but have never done that so I'm not sure how to translate the image file to the blob for insertion.

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

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

  • I got it working using the blob method... awesome.

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

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

  • Hi There,

    I am trying to use blob method to upload attachment but the dbamp is expecting file location in Body column and trying to read that as location? How do we specify that it should consider Body as the column that contains entire file in binary format? Can you please provide steps to do this way?

    The process could not be created for step 1 of job 0xC936BEB4D76DA942BDC4D2D07F03EA1B (reason: The system cannot find the file specified).

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

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

  • What version of DBAmp are you currently running?

    Can you post a screenshot of the Registry Settings page in the DBAmp Configuration Program?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Also is the body column defined as an IMAGE data type?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • The version of DBAmp is - 2.20.8

    The body column of Attachment_Load table that I pulled from Salesforce has column defined as varbinary(max).
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Also, I have inserted 'appliaction/pdf' value in ContentType column given I am trying to upload is pdf file.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • here is the registry settings page...please see the attachment
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Set the Base64 Maximum field size to a large number like 1000000. Then try to upload the attachments again.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Thanks for your prompt reply. I sure can try the suggestion you have mentioned. But if you see the error message the system is actually treating body column as file location and trying to read the file from that location. Do you think it will change after I set the value for Base64?

    The process could not be created for step 1 of job 0xC936BEB4D76DA942BDC4D2D07F03EA1B (reason: The system cannot find the file specified).
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I have tried after increasing base64 max field size and I am still receiving the below error.

    The process could not be created for step 1 of job 0xC936BEB4D76DA942BDC4D2D07F03EA1B (reason: The system cannot find the file specified). The step failed.

    It seems like it is still expecting file path in body column. Please advise.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can you run that same sf_bulkops command in a new query window and post the complete message output?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • That's interesting. It ran successfully when I ran in query window but gives below error when it is run via scheduled job. Any reason why it acts different when I run the same commad as job?

    Message
    Executed as user: SQLService. The process could not be created for step 1 of job 0xC936BEB4D76DA942BDC4D2D07F03EA1B (reason: The system cannot find the file specified). The step failed
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hi,
    I'm having the same problem as the original post: when I execute:

    exec sf_bulkops 'Insert','SALESFORCE_LIVE','Attachment_Load';

    as myself on the server with the "Body" set to "C:\Test\Test.txt" it works, when I change body to point to a network drive "X:\Public\Test\Test.txt" it fails - the file exists and I have access to it:

    Error Column = "Error: Unable to read file X:\Public\Test\Test.txt ."

    --- Starting SF_BulkOps for Attachment_Load V3.6.4
    16:11:44: Run the DBAmp.exe program.
    16:11:44: DBAmp Bulk Operations. V3.6.4 (c) Copyright 2006-2017 forceAmp.com LLC
    16:11:44: Inserting Attachment_Load (FIPSQL02\LIVE / Salesforce_Live).
    16:11:45: DBAmp is using the SQL Native Client.
    16:11:46: SOAP Headers:
    16:11:46: Error: Unable to open file. Please check name and existence.
    16:11:46: 1 rows read from SQL Table.
    16:11:46: 1 rows failed. See Error column of row for more information.
    16:11:46: 0 rows successfully processed.
    16:11:46: Errors occurred. See Error column of row for more information.
    16:11:46: Percent Failed = 100.000.
    16:11:46: Error: DBAmp.exe was unsuccessful.
    16:11:46: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe insert Attachment_Load "FIPSQL02\LIVE" "Salesforce_Live" "SALESFORCE_LIVE" " "
    --- Ending SF_BulkOps. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_BulkOps, Line 223 [Batch Start Line 0]
    SF_BulkOps Error: 16:11:44: DBAmp Bulk Operations. V3.6.4 (c) Copyright 2006-2017 forceAmp.com LLC16:11:44: Inserting Attachment_Load (FIPSQL02\LIVE / Salesforce_Live).16:11:45: DBAmp is using the SQL Native Client.16:11:46: SOAP Headers: 16:11:46: Error: Unable to open file. Please check name and existence.16:11:46: 1 rows read from SQL Table.16:11:46: 1 rows failed. See Error column of row for more information.16:11:46: 0 rows successfully processed.16:11:46: Errors occurred. See Error column of row for more information.

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

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

  • In the current version, the files cannot be on a Network Drive. This is something we are looking to add in a future release.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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