Search

SF_BulkSOQL

SF_BulkSOQL

Usage

The SF_BulkSOQL stored procedure creates and populates a local SQL table with the results of a SOQL query. It uses the Salesforce Bulk API, so the SOQL query must be valid to use with the Bulk API. For more information on SOQL, visit: https://developer.Salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/asynch_api_using_bulk_query.html

SF_BulkSOQL uses two SQL Server tables, Results and SOQL. The following sections describe each table.

Results Table

The Results table

  • holds the results of a SOQL statement in a SQL Server table locally

  • is created or recreated when SF_BulkSOQL runs

  • is specified in the second parameter of SF_BulkSOQL

SOQL Table

The SOQL table

  • holds the SOQL statement that populates the Results table

  • must be named Results table name + _SOQL (e.g., AccountsContacts_SOQL)

  • must be created prior to running SF_BulkSOQL

  • must contain one column only, named SOQL nvarchar(max), for example,

     Create Table AccountsContacts_SOQL 
 (SOQL nvarchar(max))

  • must contain one row only

  • value of one row in the SOQL field must be a valid BulkAPI SOQL statement

NOTE: Do not allow other applications to write to the same Results or SOQL table while SF_BulkSOQL is running.

Syntax

 EXEC SF_BulkSOQL 'table_server','table_name','options', '𝘴𝘰𝘲𝘭_𝘴𝘵𝘢𝘵𝘦𝘮𝘦𝘯𝘵'

where table_server is the name of your linked server and table_name is the name of the Results table. WHERE 𝘴𝘰𝘲𝘭_𝘴𝘵𝘢𝘵𝘦𝘮𝘦𝘯𝘵 is the SOQL statement that can be passed in as a fourth parameter. If you pass in a SOQL statement, you do not have to create a SOQL table; SF_BulkSOQL creates it.

Example

Follow the steps in this example to create a local AccountsContacts Results table.

  1. Create the SOQL table:

    Create Table AccountsContacts_SOQL
(SOQL nvarchar(max))

  2. Insert the SOQL statement into the SOQL column of the SOQL table:

    INSERT INTO AccountsContacts_SOQL (SOQL) Values('SelectAccount.ID, Account.Name, * FROM Contact')

  3. Run SF_BulkSOQL to populate the Results table:

    EXEC SF_BulkSOQL 'Salesforce', 'AccountsContacts'

The Results table is stored in the 2nd parameter of SF_BulkSOQL.

How to Run SF_BulkSOQL Without Using xp_cmdshell

If you do not have access to xp_cmdshell, follow these steps to run SF_BulkSOQL:

  1. Run SF_BulkSOQLPrep with the same parameters used for SF_BulkSOQL. Replace SF_BulkSOQL with the SF_BulkSOQLPrep stored procedure using the same parameters in the example above.

    If passing in the SOQL Statement to SF_BulkSOQLPrep, follow the example below, “Example Passing in SOQL statement”, but replace SF_BulkSOQL with SF_BulkSOQLPrep. You must use SF_BulkSOQLPrep if you are not using xp_cmdshell.

  2. Run the underlying BulkSOQL program directly (i.e. instead of using SF_BulkSOQL) using CmdExec. The name of the executable is DBAmpAZ.exe. It is located in the DBAmp Program Files directory. Normally the directory is C:Program FilesDBAmp, but DBAmp may be installed in a different location.

The DBAmpAZ.exe program takes the following parameters:

  • command—Must be Export.

  • Operation—must be Replicate:bulksoql.

  • Result—the name of the Result table. This must be the name of the created SOQL table, but instead of _SOQL on the end, it is _Result. For,example, if the SOQL table you created is AccountsContacts_SOQL, then the Result table must be AccountsContacts_Result.

  • SQL Server Name—the name of the SQL instance to connect to.

  • SQL Database Name—the name of the database to connect to. Enclose in double quotes if the name contains a blank.

  • Link Server Name—The name of the DBAmp link server.

  • Options—bulksoql must be specified in the options parameter.

Here is an example of a complete command:

   "C:\Program Files\CData\CData DBAmp\bin\DBAmpAZ.exe" FullCopy AccountsContacts 
   BUDDY "Salesforce Backups" SALESFORCE bulksoql

Note that even though the command appears on multiple lines in this document, you must enter the command as a single line in the job step. Also notice the use of double quotes around both the program and the database. This is required because those values contain blanks.

When setting up a job step to call the program directly, you must change the Type of the job step to: Operating System (CmdExec). Then enter your complete command in the Command text. Again, the command must be on a single line.

DBAmp.exe returns 0 for a successful completion and -1 if any rows failed. Ensure that the Process exit code of a successful command is 0 (zero). A -1 is returned when some rows succeeded and some failed. Use the error column of the table to determine the failed rows. Rows that succeeded do not need to be resubmitted.

Your command may be different depending on the install directory.

Example for Embedded Single Quotes

The following example creates a local Contacts1 Results table where the one record in the table has an embedded single quote in the last name. For this example, the last name is O’Brien.

  1. Create the SOQL table:

    Create Table Contacts1_SOQL (SOQL nvarchar(max))

  2. Insert the SOQL statement into the SOQL column of the SOQL table:

    Insert Into Contacts1_SOQL (SOQL) Values(‘Select ID, LastName from Contact WHERE LastName =’O’‘Brien’)

    Note that the WHERE clause string uses single quotes; there are no double quotes.

  3. Run SF_BulkSOQL to populate the Results table:

    EXEC SF_BulkSOQL ‘Salesforce’, ‘Contacts1’

    Notice the Results table in the second parameter of SF_BulkSOQL.

Example of Passing in SOQL Statement

Do not create a SOQL table if using this technique; SF_BulkSOQL creates the SOQL table.

The following example creates a local Leads1 results table:

Run SF_BulkSOQL to populate the Leads1 table by passing in a valid SOQL statement:

  EXEC SF_BulkSOQL 'Salesforce', 'Leads1', '', 'SELECT * FROMLead'

Notice the Results table in the second parameter of SF_BulkSOQL. Also, note that the third parameter is required for a SOQL statement to be passed to SF_BulkSOQL. If you use the fourth parameter (soql_statement), you must pass in a valid value for the third parameter (options).

Options

pkchunk—SF_BulkSOQ uses the Salesforce.com BulkAPI by default. If you would like to use the Salesforce.com BulkAPI with the pkchunking header instead, add the optional pkchunk switch. SF_BulkSOQL then submits a BulkAPI job using the pkchunking header. Only use this option for large tables. For example:

  EXEC SF_BulkSOQL 'Salesforce',' Contacts1','pkchunk'

The default batch size is 100,000. You can alter this using the batchsize parameter:

  EXEC SF_BulkSOQL 'Salesforce',' Contacts1','pkchunk, batchsize(50000)'

By default, the options parameter in SF_BulkSOQL is null.