Search

SF_BulkSOQL

SF_BulkSOQL

Usage

The SF_BulkSOQL stored procedure creates and populates a local SQL table with the results of a Salesforce Object Query Language (SOQL) query. It uses the Salesforce Bulk API, so the SOQL query must be valid Bulk API execution. For more information about SOQL, see the Salesforce document Introduction to Bulk API 2.0 and Bulk API.

SF_BulkSOQL uses two user-defined SQL Server tables: Results and SOQL.

Results Table

The Results table is a user-specified SQL Server table that stores the output of the SOQL query in a local SQL Server table. When the SF_BulkSOQL procedure runs, it creates or re-creates the Results table based on the fields that are returned by the SOQL statement.

You specify the name of the Results table in the second parameter of SF_BulkSOQL.

SOQL Table

The SOQL table is a required companion table that stores the SOQL statement used to populate the Results table.

The table name must follow this naming convention:

      ResultsTableName_SOQL (for example, AccountsContacts_SOQL)

For example, if the Results table is named AccountsContacts, the SOQL table must be named AccountsContacts_SOQL. This table must be created before you run SF_BulkSOQL.

The table must contain exactly one column, as shown in this example:

CREATE TABLE AccountsContacts_SOQL (SOQL nvarchar(max))

The table must also contain exactly one row. The value in the SOQL column must be a valid Bulk API SOQL statement. SF_BulkSOQL reads this row and executes the statement to populate the Results table.

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', 'SOQL-statement'

In this syntax:

  • The table-server parameter specifies the name of your linked server.

  • The table-name parameter specifies the name of the Results table.

  • The options parameter can be either pkchunk or queryall (see Options for details). However, by default, this parameter is null.

  • The SOQL-statement parameter specifies an SOQL statement that you can pass as the fourth parameter.

If you pass an SOQL statement as the fourth parameter, you do not need to create an SOQL table; SF_BulkSOQL creates it automatically.

Example

The following example creates a local table named AccountsContacts Results.

  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('SELECT Account.ID, Account.Name, * FROM Contact')

  3. Run SF_BulkSOQL to populate the Results table:

    EXEC SF_BulkSOQL 'Salesforce', 'AccountsContacts'

The Results table is specified as the second parameter of SF_BulkSOQL.

Running SF_BulkSOQL Without Using xp_cmdshell

If you do not have access to xp_cmdshell, you cannot run SF_BulkSOQL directly. Instead, use the SF_BulkSOQLPrep stored procedure, as follows:

  1. Run SF_BulkSOQLPrep using the same parameters that you would use for SF_BulkSOQL.

  2. If you pass the SOQL statement as a parameter, follow the earlier example for SF_BulkSOQL, but replace the procedure name with SF_BulkSOQLPrep.

    Note: SF_BulkSOQLPrep must be used whenever xp_cmdshell is not available.

  3. Run the underlying BulkSOQL program directly (that is, instead of using SF_BulkSOQL) by using CmdExec. The name of the executable file is DBAmpAZ.exe. This executable is located in the DBAmp Program Files directory. Normally the directory is C:\Program Files\CData\CData DBAmp\bin\, but your instance of DBAmp might be installed in a different location.

    The DBAmpAZ.exe executable takes the following parameters:

    • command—Must use the value Export.

    • operation—Must use the value Replicate:bulksoql.

    • result—The name of the Results table. This must match the name of the created SOQL table. However, instead of appending _SOQL to the end of the table name, you append _Result. For example, if the SOQL table that you create is AccountsContacts_SOQL, then the Results table name 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 the name in double quotes if the name contains a blank space.

    • link-server name—The name of the DBAmp link server.

    • options—Must use bulksoql in the options parameter.

Command Example

The following example shows how to execute DBAmpAZ.exe directly to run a BulkSOQL operation.

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

Important: You must enter the the command as a single line in the job step.

Double quotes are required around values that contain spaces, including the program path and database name.

When you set up a job step to call the program directly, you must set Type to Operating System (CmdExec). Then enter the complete command in a single line in the Command field.

DBAmp.exe returns 0 for a successful completion and -1 if any rows fail. Ensure that the value for Process exit code of a successful command is 0. A return value of -1 indicates that some rows succeeded and some failed. Review the Error column in the output table to identify failed rows. Rows that succeeded do not need to be resubmitted.

Using Embedded Single Quotes

The following example creates a local Contacts1 results table where the one record in the table contains an embedded single quote in the last name. For this example, the last name of the contact 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''')

    In this example, the WHERE-clause string uses single quotes because it is a string literal in SOQL (and SQL). The doubled single quote ('') represents an escaped apostrophe inside the string.

  3. Run SF_BulkSOQL to populate the Results table:

    sql EXEC SF_BulkSOQL 'Salesforce', 'Contacts1'

Notice that the Results table is in the second parameter of SF_BulkSOQL.

Passing an SOQL Statement

If you pass the SOQL statement directly, do not create an SOQL table. SF_BulkSOQL creates it automatically.

The following example runs SF_BulkSOQL to create a local Results table named Leads1:

EXEC SF_BulkSOQL 'Salesforce', 'Leads1', '', 'SELECT * FROM Lead'

Notes:

  • The Results table is the second parameter of SF_BulkSOQL.

  • The third parameter (options) is required for an SOQL statement to be passed to SF_BulkSOQL. As mentioned previously, this parameter is null by default. Other valid values are pkchunk and queryall.

  • If you use the fourth parameter (soql_statement), you must pass a valid value for the third parameter.

Options

The section describes two options that you can use with the SF_BulkSOQL stored procedure.

  • pkchunk: By default, SF_BulkSOQL uses the Salesforce Bulk API. If you want to use the Bulk API with the PK chunking header enabled, add the optional pkchunk switch, as shown below:

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

    When you specify this option, SF_BulkSOQL submits a Bulk API job that includes the PK chunking header. Use this option only for large tables, where chunking improves performance and reduces the likelihood of timeouts.

    The default batch size is 100,000 records. You can change this value by using the batchsize(n) parameter, as shown in this example:

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

  • queryall: By default, SF_BulkSOQL does not include archived or deleted records from the Salesforce.com object when it populates the Results table. To include archived and deleted records in the SOQL query results, use the queryall switch, as shown below:

     EXEC SF_BulkSOQL 'Salesforce',' Contacts1', 'queryall'