• Using the Four-Part Object Name in SQL
• Using OPENQUERY and SOQL
• Passing Parameters in SOQL Queries
• Joining Salesforce Tables

Using the Four-Part Object Name in SQL

To refer to a Salesforce.com object in a SQL statement, use the four-part object name:

• Linked server name—the name you specified when installing DBAmp
• Database name—use CData for all queries
• Schema name—use Salesforce for all queries.
• Table name—name of the Salesforce object

In other word, the two parameters that you must choose are the linked server name and the table name. You cannot change either CData or Salesforce.

So, as a template:

SELECT * FROM 𝘓𝘪𝘯𝘬𝘦𝘥𝘚𝘦𝘳𝘷𝘦𝘳𝘕𝘢𝘮𝘦.CData.Salesforce.𝘛𝘢𝘣𝘭𝘦𝘕𝘢𝘮𝘦

For example, to select all rows and columns of the Contact object in a linked server called SALESFORCE_LS:

SELECT * FROM SALESFORCE_LS.CData.Salesforce.Contact

The SQL Server Distributed Query Optimizer chooses a plan for every SQL statement that executes. Often, the plan chosen is the most efficient and there is no need to modify your SQL. Should you suspect a poorly performing plan, use the Query Analyzer to enter the text of the SQL statement. Remember to use the four-part naming convention for Salesforce.com tables.

Note the following when using SQL:

• Do not enter unquoted date literals. Instead, use Transact SQL syntax for date literals (i.e. include quotes).

• You can use * to indicate all columns.

• Follow Transact SQL rules for WHERE clause AND/OR precedence. Parentheses are only needed when explicit grouping is needed and are not required (unlike SOQL).

• User and Case are keywords in Transact SQL and must be quoted when used as a four-part name to refer to the Salesforce.com object.

Using OPENQUERY and SOQL

There are two ways to query real time data from Salesforce:

• the four-part object name with SQL
• the OPENQUERY clause with SOQL.

When additional JOIN performance is needed, consider using the OPENQUERY clause with DBAmp. Using OPENQUERY allows you to pass Salesforce.com SOQL statements (not SQL) directly to DBAmp. You can find a full description of the SOQL language on Salesforce.com.

Using OPENQUERY with SOQL can make dramatic performance differences on joined data. With SOQL, the join is performed on the Salesforce.com server as opposed to locally on your SQL server.

For example,

SELECT * FROM OPENQUERY(Salesforce,
'SELECT Type, BillingCountry,
GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,
COUNT(id) accts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)')

DBAmp currently supports both child-to-parent relationship queries and parent-to-child queries. For example,

 SELECT * from OPENQUERY(Salesforce,
'SELECT Account.Name, (SELECT OwnerID FROM Account.Notes) FROM Account')
 SELECT * from OPENQUERY(Salesforce,
'SELECT ID, Who.FirstName, Who.LastName FROM Task');

Passing Parameters in SOQL Queries

To use parameters in a SOQL query, you must use the EXECUTE statement of T-SQL. Here is an example:

CREATE TABLE RevByAccount
(Name nvarchar(255) NULL,
AnnualRevenue decimal(18,0) NULL);

DECLARE @MinRev INT
SET @MinRev = 20
INSERT RevByAccount

EXEC('SELECT Name, AnnualRevenue FROM **Account**WHERE AnnualRevenue \> ?',
@MinRev) AT Salesforce

Go

Joining Salesforce Tables

DBAmp fully supports JOIN. You can join tables exactly as you do in any SQL code. Note that performing joins on parent-child relationships is much more efficient than joins of unrelated objects.