Tips for using SQL Server with Salesforce SOQL

Contents

1. Simple SELECT queries in SQL Server do not work
2. Bulk inserting data using the SOQL driver
3. UPDATE and DELETE statement bulking
4. Easysoft transactional support
5. Lazy schema validation
6. Limitations of Microsoft's OLEDB for ODBC Provider

Simple SELECT queries in SQL Server do not work

Let's say you are trying to run this query in SQL Server:

SELECT Id FROM SFSOQL8...Account

SQL Server converts that SQL into:

SELECT "Tbl1002"."Id" "Col1004" FROM "Account" "Tbl1002"

As the Salesforce SOQL language does not support the renaming of tables and columns in that way, you end up with this error:

OLE DB provider "MSDASQL" for linked server "SFSOQL8" returned message "[Easysoft][Salesforce SOQL ODBC Driver]General error: Query Failed: 'MALFORMED_QUERY: only aggregate expressions use field aliasing'". Msg 7320, Level 16, State 2, Line 1 Cannot execute the query "SELECT "Tbl1002"."Id" "Col1004" FROM "Account" "Tbl1002"" against OLE DB provider "MSDASQL" for linked server "SFSOQL8".

SQL Server has two other methods for sending SOQL to the Easysoft driver:

1. OPENQUERY, which can be used with local tables to join remote and local data.
2. EXEC, which can be used with parameters.

To demonstrate these methods, we'll start with a SOQL table join:

SELECT Id, Amount, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity

In SQL Server, you could run either of the following queries:

SELECT * FROM OPENQUERY(SFSOQL8, 'SELECT Id, Amount, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity')

—Or—

EXEC ('SELECT Id, Amount, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity') at SFSOQL8

There is almost no difference in performance as the SOQL you have written is passed straight to the Salesforce server.

Likewise, all the SOQL functions are also available using the same methods:

SELECT * FROM OPENQUERY(SFSOQL8, 'select Id, Name, DISTANCE(CustLocation__c , GEOLOCATION(37.775,-122.418), ''mi'') from Account where Name like ''Bur%'' ')

SELECT * FROM OPENQUERY(SFSOQL8, '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)')

If you do pass in invalid SOQL, the Easysoft SOQL driver will return the error directly from Salesforce. For example:

SELECT * FROM OPENQUERY(SFSOQL8, 'SELECT Id, Name, DISTANCE(CustLocation__c , GEOLOCATION(37.775,-122.418), ''mo'') FROM Account WHERE Name LIKE ''Bur%'' ')

OLE DB provider "MSDASQL" for linked server "SFSOQL8" returned message "[Easysoft][Salesforce SOQL ODBC Driver]General error: Query Failed: 'INVALID_FIELD: 
SELECT Id, Name, DISTANCE(CustLocation__c , GEOLOCATION(37.775
                          ^
ERROR at Row:1:Column:27
Invalid distance unit: mo. Valid unit: 'mi', 'km''".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "select Id, Name, DISTANCE(CustLocation__c , GEOLOCATION(37.775,-122.418), 'mo') from Account where Name like 'Bur%' " against OLE DB provider "MSDASQL" for linked server "SFSOQL8". 

More information about the SOQL Language can be found here.

Bulk inserting data using the SOQL driver

Within the Salesforce SOAP API, there's a function that allows you to insert up to 200 rows of data from a single SOAP API call. The Easysoft Salesforce SOQL ODBC driver makes use of this function and lets you use SQL Server T-SQL to bulk load up to 200 rows at a time.

In my example, I'm going to add new records to the Account object within Salesforce. This is a very basic example with only a few columns of data, but I hope this explains how bulk inserts are possible from SQL Server. In SQL Server, I have a local table called Account which looks like:

begin
    declare @BlockCount as int
    declare @IsPosted as int
    declare @PrmName As nvarchar(255)
    declare @PrmAddress As nvarchar(255)
    declare @PrmTown As nvarchar(40)
    declare @PrmPostCode As nvarchar(30)
    declare @PrmDescription As nvarchar(255)
	declare @SQL as nvarchar(255)

	set @BlockCount=0
	set @SQL='INSERT INTO Account (Name, BillingStreet, BillingCity, BillingPostalCode, Description ) VALUES ( ?, ?, ?, ?, ? )'

	declare select_cursor cursor local FORWARD_ONLY for 
		SELECT AccName, "Property Description", "Address", Town, PostCode FROM Account ORDER BY Id

	open select_cursor
	fetch next from select_cursor into @PrmName, @PrmAddress, @PrmTown, @PrmPostCode, @PrmDescription
	while @@FETCH_STATUS=0
	begin
		if (@BlockCount=0)
		Begin
			set @IsPosted=0
			exec('Begin Trans') at SFSOQL8
		end
		set @BlockCount=@BlockCount+1

		exec (@SQL, @PrmName, @PrmAddress, @PrmTown, @PrmPostCode, @PrmDescription) at SFSOQL8

		if (@BlockCount=200)
		Begin
			set @IsPosted=1
			exec('Commit') at SFSOQL8
		end
		fetch next from select_cursor into @PrmName, @PrmAddress, @PrmTown, @PrmPostCode, @PrmDescription

	end

	if (@IsPosted=0)
	begin
		exec('Commit') at SFSOQL8
	end
	
	close select_cursor;
	deallocate select_cursor;
end

This T-SQL works by reading my local Account table into a cursor.

At the start of the first block of 200 rows, the Begin Trans is called, which tells the Easysoft driver that any data passed to it will be held until either a COMMIT or a ROLLBACK is called.

The EXEC function sends each row found in the cursor to the Easysoft driver. The driver then builds up the required SOAP API call. When 200 rows have been sent to Easysoft, I send a COMMIT, which causes the driver to send the SOAP API call to Salesforce.

When the end of the cursor is reached, if there are any records sent to the Easysoft driver that have not been passed to Salesforce, I send a final COMMIT. The cursor is then closed and deallocated.

The 200 row limit is an internal limit within Salesforce. If you try to send more than 200 rows you will get a Salesforce error. The Easysoft driver has no built in limits, so if Salesforce increase the 200 row limit in future versions of the SOAP API, the Easysoft driver will automatically work with the new limit.

When using this bulk insert method there is no limit to the number of tables you can insert data to in a single block, so the following T-SQL will work:

Begin
	exec('Begin Trans') at SFSOQL8
	exec('INSERT INTO Account ( Name ) VALUES (''Richard Test'') ') AT SFSOQL8
	exec('INSERT INTO Opportunity ( Name, StageName, CloseDate ) VALUES (''Richard Test'', ''My Stage'', ''2018-08-14T10:07:00Z'') ') at SFSOQL8
	exec('Commit') at SFSOQL8
End

When using the Begin Trans Commit method for inserts you can only send INSERT statements. You cannot mix and match inserts and updates as this is not supported in the Salesforce SOAP API.

UPDATE and DELETE statement bulking

Our SOQL driver has a built in method for bulking data transactions, when used with an UPDATE or a DELETE statement.

This example shows how I update a custom column to a set value.

EXEC ('UPDATE Account SET CustomerPriority__c=''low'' WHERE CustomerPriority__c=NULL') AT SFSOQL8

Salesforce does not support UPDATEs or DELETEs using SOQL, so the Easysoft driver has to convert the query into a SOQL SELECT. The rows returned from the SELECT are then bulked up into blocks of up to 200 and sent a block at a time for an UPDATE or DELETE statement.

Easysoft transactional support

The Easysoft Salesforce SOQL ODBC driver supports a single level of transaction only. So a Begin Trans followed by some INSERTs can be sent to Salesforce with a COMMIT or thrown away within the driver by using a ROLLBACK.

Lazy schema validation

In your SQL Server linked server properties under the Server Options section is an option for Lazy Schema Validation. By default, this is set to FALSE which causes SQL Server when running a SELECT statement to send the statement twice. The first time it is sent SQL Server uses the details passed back to build up the metadata about your result set. Then the query is sent again. This is quite an expensive overhead, so we recommend that you set Lazy Schema Validation to TRUE, which means that only one SELECT is sent, which gets both the metadata and the data. This also reduces the number of Salesforce API calls being made.

Limitations of Microsoft's OLEDB for ODBC Provider

Details about the limitations of the OLEDB for ODBC Provider can be found on here:

https://msdn.microsoft.com/en-us/library/ms719628(v=vs.85).aspx