Usage

The SF_BulkSOQL stored procedure creates and populates a SQL table with the results of a SOQL query in the remote database of the remote SQL Server Instance. 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 in a remote database of the remote SQL Server instance • 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 • is created in the remote database of the remote SQL Server Instance • is named Results table name + _SOQL (e.g., AccountsContacts_SOQL) • is created automatically by the SF_BulkSOQL stored procedure

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

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 is passed in as a fourth parameter. Use_remote 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 an AccountsContacts Results table 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 2nd parameter of SF_BulkSOQL.

Options

pkchunk—SF_BulkSOQ uses the Salesforce.com BulkAPI by default. If you want 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','AccountsContacts','pkchunk', 'Select Account.ID, Account.Name, * FROM Contact', 1

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

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

By default, the options parameter in SF_BulkSOQL is NULL.