Search

SF_BulkSOQL

SF_BulkSOQL

Usage

The SF_BulkSOQL stored procedure creates and populates an SQL table with the results of a Salesforce Object Query Language (SOQL) query in the remote database of the remote SQL Server instance. This procedure uses the Salesforce Bulk API, so the SOQL query must be valid to use with the Bulk API. For more information about SOQL, see the Salesforce Bulk API Developer Guide.

SF_BulkSOQL uses two SQL Server tables: Results and SOQL.

Results Table

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

SOQL Table

The SOQL table is an SQL Server table that stores the SOQL statement that populates the Results table. When the SF_BulkSOQL stored procedure runs, it automatically creates the SOQL table in the remote database of the remote SQL Server instance.

The SOQL table name is based on the Results table name with the _SOQL suffix appended (for example, AccountsContacts_SOQL).

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', 'use_remote'

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 SOQL-statement parameter specifies the SOQL statement that is passed in as a fourth parameter.

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

  • The use_remote parameter specifies that you are targeting a database with this remote stored procedure. This parameter must be set to 1.

Note: You must pass in a valid SOQL statement as the fourth parameter. Do not create the SOQL table in the remote database of the remote SQL Server Instance.

Example

The following example creates a Results table named AccountsContacts in the remote database of the remote SQL Server instance.

EXEC SF_BulkSOQL 'Salesforce', 'AccountsContacts', '', 'Select Account.ID, Account.Name, * FROM Contact', 1

The Results table is stored in the second parameter of SF_BulkSOQL.

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:

    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.

    EXEC SF_BulkSOQL 'Salesforce', 'AccountsContacts', 'pkchunk', 'Select Account.ID, Account.Name, * FROM  Contact', 1

    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', 'AccountsContacts', 'pkchunk,batchsize(50000)', 'Select Account.ID, Account.Name, * FROM Contact', 1

  • 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', 1