Tips For Using SQL Server with Salesforce

Table of Contents

1. Overview
2. WHERE Clause
3. Multiple Table Joins
4. Local Table Attached to a Remote Table
5. Insert, Update and Delete
6. Update
7. Update with Parameters
8. Inserting a New Record and Getting a BLOB Error
9. Getting the Salesforce Id for the Last Record You Inserted
10. Updating SQL Server Data when Salesforce Data Changes
11. Lazy Schema Validation
12. Limitations of Microsoft's OLEDB for ODBC Provider
13. How do I find records with a line feed (newline) in the billing address?
14. Can I see which tables are available through the Easysoft software?
15. Can I see which columns are available through the Easysoft software?
16. Can I programmatically create a linked server?

Overview

This document gives some tips on using SQL Server with Salesforce. The components used to connect SQL Server to Salesforce are a SQL Server Linked Server and the Easysoft Salesforce ODBC Driver. How you connect SQL Server to Salesforce is described in this article. For the examples in this document, the Linked Server name (which you reference in your SQL commands) used is SF8.

All the SQL in this document was tested against SQL Server 2017 and the Easysoft Salesforce ODBC driver versions 2.0.0 to 2.0.7.

The SQL Server functions OPENQUERY and EXEC (EXECUTE) were introduced into SQL Server 2008 and these functions are compatible with all versions of SQL Server after 2008.

We have written this document in response to number of a queries received by our Support Team regarding connecting SQL Server through Easysoft to Salesforce. However, the SQL examples should also be useful for Linked Server connections that use a different ODBC driver and backend.

If you would like to contribute to this document, please email your submission to .

WHERE Clause

A common issue reported to us is "A simple WHERE clause takes a long time to return only one row". For example:

select Id, FirstName, LastName from SF8.SF.DBO.Contact where Id='00346000002I95MAAS'

SQL Server converts the above query and sends this to the Salesforce ODBC driver:

select Id, FirstName, LastName from SF.DBO.Contact

The WHERE clause is always removed, which forces the ODBC driver to return all the rows for that table. Then SQL Server filters them locally to give you the row(s) required. It does not seem to matter what WHERE clause you have specified, this is never passed on to the ODBC driver.

The simple solution to this is to use the SQL Server OPENQUERY function instead. For example:

select * from OPENQUERY(SF8,'select Id, FirstName, LastName from SF.DBO.Contact where Id=''00346000002I95MAAS'' ')

All the SQL you run inside the OPENQUERY function is passed straight to the driver, including the WHERE clause.

Multiple Table Joins

Here is a simple two table join where both the tables are coming back from the linked server.

select a.[Name], BillingStreet, c.[Name] from SF8.SF.DBO.Account a, SF8.SF.DBO.Contact c where a.Id=c.AccountID and a.[Name] like 'United%'

SQL Server sends the following queries to the ODBC driver.

select * from Account
select * from Contact

SQL Server does this to get a list of column names and data types. It then goes on to send these queries to the ODBC driver.

SELECT "Tbl1001"."Id" "Col1042","Tbl1001"."Name" "Col1044","Tbl1001"."BillingStreet" "Col1046" FROM "SF"."DBO"."Account" "Tbl1001" ORDER BY "Col1042" ASC
SELECT "Tbl1003"."AccountId" "Col1057","Tbl1003"."Name" "Col1058" FROM "SF"."DBO"."Contact" "Tbl1003" ORDER BY "Col1057" ASC

The data from both queries is returned to local tables, then the WHERE clause is placed on the Account table and the data from both tables is joined and returned.

Again the use of OPENQUERY ensures the SQL you write gets passed directly to the ODBC driver, so, instead, in SQL Server you would run:

select * from OPENQUERY(SF8,'select a.[Name], BillingStreet, c.[Name] from SF.DBO.Account a, SF.DBO.Contact c where a.Id=c.AccountID and a.[Name] like ''United%'' ')

You need a slight modification, because SQL Server cannot handle multiple columns with the same "Name", so you need to rename one of those columns. For example:

select * from OPENQUERY(SF8,'select a.[Name], BillingStreet, c.[Name] as FullName from SF.DBO.Account a, SF.DBO.Contact c where a.Id=c.AccountID and a.[Name] like ''United%'' ')

This forces the ODBC driver to process the entire SQL in one go and only return the required results.

Local Table Attached to a Remote Table

In this example, the local table was created by running.

select * into LocalAccount from SF8.SF.DBO.Account

The join of the two tables now looks like.

select a.[Name], BillingStreet, c.[Name] as FullName from LocalAccount a, SF8.SF.DBO.Contact c where a.Id=c.AccountID and a.[Name] like 'United%'

This causes SQL Server to send the following query three times to the ODBC driver.

select * from Contact

In at least one of those queries, SQL Server asks for all of the data in the table. Then SQL Server goes on to ask for:

SELECT "Tbl1003"."Name" "Col1008" FROM "SF"."DBO"."Contact" "Tbl1003" WHERE ?="Tbl1003"."AccountId"

SQL Server then passes to the ODBC driver a list of AccountIds from the LocalAccount table in place of the "?" parameter where the LocalAccount.[Name] column matches the LIKE clause.

A faster way where the ODBC table is the second table in the query, is to only get the columns you need from the ODBC table. This can be done by using the OPENQUERY function. For example:

select a.[Name], BillingStreet, c.[Name] as FullName from LocalAccount a, openquery(SF8,'select [Name], AccountId from SF.DBO.Contact') c where a.Id=c.AccountID and a.[Name] like 'United%'

Whilst this still gets all the rows from the Contact table, it only gets the needed columns and is therefore faster than the standard query.

Another possible way would be to use a cursor and a temporary table. For example:

Begin
      declare @AccountId as varchar(20)
      declare @SQL as varchar(1024)

      -- Create a temporary table to store the Account information. The Id check ensures 0 rows of data are returned
      select * into #LocalContact from openquery(SF8,'select [Name], AccountId from SF.DBO.Contact where Id=''000000000000000000'' ')
      
      -- Set up the cursor      
      declare selcur cursor for
            select distinct Id from LocalAccount where [Name] like 'United%'
      
      	open selcur
      	fetch next from selcur into @AccountId
      	while @@FETCH_STATUS=0
      	Begin
      		select @SQL ='insert into #LocalContact select [Name], '''+@AccountId+''' from OPENQUERY(SF8,''select [Name] from Contact where AccountId=''''' + @AccountId + ''''' '')'
      		exec (@SQL)
      		
      		fetch next from selcur into @AccountId
      	End
      	close selcur
      	deallocate selcur
	
      	-- Next, join your tables and view the data      	
      	select a.[Name], BillingStreet, c.[Name] as FullName from LocalAccount a, #LocalContact c where a.Id=c.AccountID and a.[Name] like 'United%'
      
      	-- Don't forget to remove the temporary table
      	drop table #LocalContact
      
      End

This method can be several times faster than the OPENQUERY method shown in the previous example, if the WHERE clause being passed to the Easysoft ODBC driver uses an index in Salesforce.

Insert, Update and Delete

If you are running a query that is not a SELECT query then the best way to do this is to use the SQL Server EXEC function. If your linked server cannot use EXEC, you will get a message similar to:

Server 'SF8' is not configured for RPC.

To use EXEC, right-click on your linked server and chose properties. In the "Server Options" section, set "RPC Out" to "True". You can then use the EXEC function.

Update

Let's say you have this statement in SQL Server:

UPDATE SF8.SF.DBO.Contact SET LastName='James' WHERE Id='00346000002I95MAAS'

SQL Server sends this SQL to the ODBC driver.

select * from "SF"."DBO"."Contact"

All the records are retrieved and SQL Server then sends this statement to the ODBC driver.

UPDATE "SF"."DBO"."Contact" SET "LastName"=? WHERE "Id"=? AND "LastName"=?

SQL Server is doing that to ensure that the record does not get changed between the time you ran the query and the time the UPDATE is executed. A faster method is to use the SQL Server EXEC function. For example:

exec ('update SF.DBO.Contact set LastName=''James'' where Id=''00346000002I95MAAS''' ) at SF8 

SQL Server sends the ODBC driver the entire string you have entered, so the query is executed without selecting the entire table.

Update with Parameters

Say you have:

Begin
	declare @Id varchar(20)='00346000002I95MAAS'
	declare @LastName varchar(20)='James'
	update SF8.SF.DBO.Contact set LastName=@LastName where Id=@Id
End

This works exactly the same way as described in the Update notes. However, the syntax when using the EXEC function changes:

Begin
      	declare @Id varchar(20)='00346000002I95MAAS'
      	declare @LastName varchar(20)='James'
	exec ('update SF.DBO.Contact set LastName=? where Id=?', @LastName, @Id)
      		at SF8
End

Where you have a column such as LastName= you put a ? in place of @LastName to represent what you are going to pass into the parameter. The parameters are then listed after the UPDATE statement in the order in which they need to be read.

Inserting a New Record and Getting a BLOB Error

Say you are trying to run:

insert into SF8.SF.DBO.Contact ( FirstName, LastName ) values ('Easysoft','Test')

SQL Server sends this to the ODBC driver:

select * from "SF"."DBO"."Contact"

This is done twice. The first time this is run, SQL Server is checking to see if the result set is updateable. The second time this is sent, SQL Server moves to an empty record after the last record returned and tries to do a positional INSERT, which gives an error.

OLE DB provider "MSDASQL" for linked server "SF8" returned message "Query-based insertion or updating of BLOB values is not supported.".

This message is returned because a positional insert tries to insert all the columns with NULL values except for the ones you have specified in your INSERT statement, and in the case of the Contact table, there is a BLOB (Long Text Area in Salesforce ), which the OLE DB Provider from Microsoft does not support. The Easysoft Salesforce ODBC driver supports the inserting of all fields within Salesforce where you have permission to insert data. To get around this, all you need to do is to use EXEC.

exec ('insert into SF.DBO.Contact ( FirstName, LastName ) values (''Easysoft'',''Test'')') at SF8

This just sends the INSERT straight through to the ODBC driver.

Getting the Salesforce Id for the Last Record You Inserted

We have been asked by a few of our customers what is the easiest method to get the Id of the row that was just inserted. This example shows how you can get the Id of the last record you inserted into the "Contact" table.

Begin
	declare @Id varchar(20)='00346000002I95MAAS'
      	declare @FirstName varchar(20)='Easysoft'
      	declare @LastName varchar(20)='Test'
      	declare @FindTS varchar(22)=convert(varchar(22),GETUTCDATE(),120)
      	declare @SQL as varchar(1024)
      
      	exec ('insert into SF.DBO.Contact (FirstName, LastName ) values (?, ?)', @FirstName, @LastName ) at SF8

      	select @SQL='select Id from openquery(SF8, ''select top 1 c.Id from [User] u, Contact c where u.Username=CURRENT_USER and c.CreatedDate>={ts '''''+@FindTS+'''''} and c.CreatedById=u.Id order by c.CreatedDate desc'')'

      	exec (@SQL) 

End

When a record is created in Salesforce, the "CreatedDate" column contains a timestamp that is the UTC (Coordinated Universal Time) the record was created and not necessarily your current date / time. The @FindTs string is set to the UTC before the INSERT takes place, so when the SELECT to get the Id is called, it is only looking at the rows inserted after the @FindTS was set.

During the SELECT, the Easysoft CURRENT_USER function is also used to limit the rows returned from Salesforce to only the user that has inserted the data.

Updating SQL Server Data when Salesforce Data Changes

This section shows you how to create a new SQL Server table based upon the structure of a Salesforce table and update that table when there are changes in that Salesforce table.

create procedure SFMakeLocal( @Link varchar(50), @Remote varchar(50), @Local varchar(50), @DropLocal int) as
          declare @SQL as nvarchar(max)
          begin
              /* Imports the data into a local table */
              /* Set DropLocal to 1 to drop the local table if it exists */
      
              if OBJECT_ID(@Local, 'U') IS NOT NULL 
              begin
                  if (@DropLocal=1) 
                  begin
                      set @SQL='DROP TABLE dbo.'+@Local
                      exec ( @SQL)
                  end
              else
                  RAISERROR(15600,1,1, 'Local table already exists')
                  RETURN  
              end
      
              set @SQL='select * into dbo.'+@Local+' from OPENQUERY('+@Link+',''select * from '+@Remote+''')'
              
              exec(@SQL)
      		select 'Local Table :'+@Local+' created.'
          end
      
      -- @Link Your SQL Server linked server
      -- @Remote The name of the table within Salesforce
      -- @Local The local table you want the data to be stored in
      -- @DropLocal Set to 1 if the table exists and you want to drop it

Run the procedure to copy the record structure from the Salesforce table into the local table and then transfer all the Salesforce data. This example command uses the Account table. This process can take quite a while depending on the amount of data you have in the Salesforce table.

SFMakeLocal 'SF8','Account','LocalAccount', 0

The arguments are:

The next step is to create two more procedures that will update the local table if any data is updated or inserted into the Salesforce table:

create procedure SFUpdateTable ( @Link varchar(50), @Remote varchar(50),
      create procedure SFUpdateTable  
      @Link varchar(50), @Remote varchar(50), @LocalTable varchar(50) 
      as
          begin
              -- Updates the data into a local table based on changes in Salesforce.
      
      		declare @TempDef as varchar(50)='##EasyTMP_'
      		declare @TempName as varchar(50)
      		declare @TempNumber as decimal
      
      		declare @CTS as datetime=current_timestamp
      		declare @TTLimit int = 100
              declare @MaxCreated as datetime
              declare @MaxModified as datetime
              declare @SQL as nvarchar(max)
      		declare @RC as int
      
      		-- The first step is to create a global temporary table.
      
      		set @TempNumber=datepart(yyyy,@CTS)*10000000000+datepart(mm,@CTS)*100000000+datepart(dd,@CTS)*1000000+datepart(hh,@CTS)*10000+datepart(mi,@CTS)*100+datepart(ss,@CTS)
      		set @TempName=@TempDef+cast(@TempNumber as varchar(14))
      
      		while OBJECT_ID(@TempName, 'U') IS NOT NULL 
              begin
                  RAISERROR (15600,1,1, 'Temp name already in use.')
                  RETURN  
              end
      
      		set @SQL='select * into '+@TempName+' from '+@LocalTable+' where 1=0'
      
      		create table #LocalDates ( ColName varchar(20), DTS datetime)
              set @sql='insert into #LocalDates select ''Created'', max(CreatedDate) from '+@LocalTable
      		exec (@sql)
              set @sql='insert into #LocalDates select ''Modified'', max(LastModifiedDate) from '+@LocalTable
      		exec (@sql)
      
      		select @MaxCreated=DTS from #LocalDates where ColName='Created'
      		select @MaxModified=DTS from #LocalDates where ColName='Modified'
      
      		drop table #LocalDates
      
              set @SQL='select * into '+@TempName+' from openquery('+@Link+',''select * from '+@Remote+' where CreatedDate>{ts'''''+convert(varchar(22),@MaxCreated,120)+'''''}'')'
              exec(@SQL)
      		exec SFAppendFromTemp @LocalTable, @TempName
      
      		set @SQL='drop table '+@TempName
      		exec (@SQL)
      
              set @SQL='select * into '+@TempName+' from openquery('+@Link+',''select * from '+@Remote+' where LastModifiedDate>{ts'''''+convert(varchar(22),@MaxModified,120)+'''''} and CreatedDate<={ts'''''+convert(varchar(22),@MaxCreated,120)+'''''}'')'
      		exec (@SQL)
              exec SFAppendFromTemp @LocalTable, @TempName
      
      		set @SQL='drop table '+@TempName
      		exec (@SQL)
      
      
          end
      create procedure SFAppendFromTemp(@Local varchar(50), @TempName varchar(50)) as 
      begin
      
      
          /* Uses the temp table to import the data into the local table making sure any duplicates are removed first */
      
          declare @Columns nvarchar(max)
          declare @ColName varchar(50)
          declare @SQL nvarchar(max)
      
          set @sql='delete from '+@Local+' where Id in ( select Id from '+@TempName+')'
          exec (@SQL)
      
          set @Columns=''
      
          declare col_cursor cursor for 
              select syscolumns.name from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id where sysobjects.xtype = 'u' and  sysobjects.name = @Local
      
          open col_cursor
          fetch next from col_cursor into @ColName
          while @@FETCH_STATUS=0
          Begin
              set @Columns=@Columns+'['+@ColName+']'
              fetch next from col_cursor into @ColName
              if (@@FETCH_STATUS=0)
                  set @Columns=@Columns+', '
          End
          close col_cursor
          deallocate col_cursor
      
          set @sql='insert into '+@Local+' (' +@Columns+') select '+@Columns+' from '+@TempName
          exec (@sql)
      
      end
      
      -- Two procedures are used to get the data from a remote table. 1) SFUpdateTable, which
      -- copies the data into a temporary table. 2) SFAppendFromTemp, which appends
      -- the data from the temporary table into the local table.
      
      -- @Link Your SQL Server linked server name
      -- @Remote The name of the table within Salesforce
      -- @Local The local table where you want the data to be stored in
      -- @TempName A name of a table that can be used to temporary store data. Do not
      -- use an actual temporary table name such as #temp, this will not work.

To test this, run:

SFUpdateTable 'SF8','Account','LocalAccount'

This example can be used with any Salesforce table a user has access to.

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 to send SELECT statements twice. The first time the query is sent, SQL Server uses the details passed back to build up metadata about your result set. Then the query is sent again. This is quite an expensive overhead, so Easysoft would recommend that you set "Lazy Schema Validation" to TRUE, which means that only one query is sent, retrieving both metadata and result set in one go. This also saves on 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 at:

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

How do I find records with a line feed (newline) in the billing address?

By using some of the Easysoft driver's internal functions, you can easily find records where the billing address has a line feed within the record. For example:

select * from openquery(sf8,'select Id, Name, {fn POSITION({fn CHAR(10)} IN BillingStreet)} LinePos from Account where {fn POSITION({fn CHAR(10)} IN BillingStreet)} >0')

POSITION(x) This function looks for the position of x within the column specified.

CHAR(X) This function returns the character with the ASCII value of x.

More information about the functions available in our Salesforce ODBC driver can be found here

Can I see which tables are available through the Easysoft software?

To get a list of tables that you can access, run:

select * from openquery(SF8,'select TABLE_NAME from INFO_SCHEMA.TABLES')

Can I see which columns are available through the Easysoft software?

You can get a list of columns that are in table by running:

select * from openquery(SF8,'select * from INFO_SCHEMA.COLUMNS where TABLE_NAME=''Account'' ')

begin
    declare @Table nvarchar(max)
	
	declare table_cursor cursor for 
        select TABLE_NAME from openquery(SF8,'select TABLE_NAME from INFO_SCHEMA.TABLES')

    open table_cursor
    fetch next from table_cursor into @Table
    while @@FETCH_STATUS=0
    Begin
		exec ('select * from INFO_SCHEMA.COLUMNS where TABLE_NAME=?', @Table) at SF8
		fetch next from table_cursor into @Table
	End

    close table_cursor
    deallocate table_cursor

end