Tips for using SQL Server with Salesforce

Contents

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 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 these rows locally to give you the rows you asked for. It does not seem to matter what WHERE clause you specify, this is never passed on to the ODBC driver.

A workaround 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 ODBC 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.

SQL Server 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

SQL Server returns the data from both queries to local tables, then places the WHERE clause on the Account table. SQL Server then joins and returns the data from both tables.

Again, using OPENQUERY ensures your SQL 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%'' ')

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 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 a list of AccountIds from the LocalAccount table instead of the "?" parameter value (the LocalAccount.[Name] column matches the LIKE clause).

A faster way when 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 method is 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 ODBC driver uses an index in Salesforce.

INSERT, UPDATE, and DELETE statements

The best way to do run INSERT, UPDATE, and DELETE statements is to use the SQL Server EXEC function. If your linked server cannot use EXEC, you'll 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 statements

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 does this 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

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 notes on UPDATE statements. 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're 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're trying to run:

INSERT INTO SF8.SF.DBO.Contact (FirstName, LastName) VALUES ('Easysoft', 'Test')

SQL Server sends this SQL to the ODBC driver:

SELECT * FROM "SF"."DBO"."Contact"

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

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

SQL Server returns this message because the positional INSERT tries to insert all the columns with NULL values except for the ones you have specified. 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. To work 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

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 Coordinated Universal Time (UTC) the record was created. 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.

The Easysoft ODBC driver's CURRENT_USER function limits the rows returned from Salesforce to those that belong to the user who 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:

Argument Value
SF8 The SQL Server Linked Server name.
Account The Salesforce table name you wish to use to read the structure and the data from.
LocalAccount The name of your table in SQL Server.
0 This default value can be changed to 1 if you add more custom columns into Salesforce and you wish to drop the local table to create it again with the new columns.

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, there's 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 the 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(n) This function looks for the position of n within the column specified.

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

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

Can I find out 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 find out 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'' ')

Using this method you can only get a list of the columns that belong to the table you specify in the WHERE clause. If you want a full list of columns for all tables, run:

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

Can I programmatically create a linked server?

Yes. There are lots of examples of this on the web, for example:

http://www.sqlservercentral.com/articles/Linked+Servers/142270/?utm_source=SSC