Tips For Using SQL Server with Salesforce
Table of Contents
- Overview
- WHERE Clause
- Multiple Table Joins
- Local Table Attached to a Remote Table
- Insert, Update and Delete
- Update
- Update with Parameters
- Inserting a New Record and Getting a BLOB Error
- Getting the Salesforce Id for the Last Record You Inserted
- Updating SQL Server Data when Salesforce Data Changes
- Lazy Schema Validation
- Limitations of Microsoft's OLEDB for ODBC Provider
- How do I find records with a line feed (newline) in the billing address?
- Can I see which tables are available through the Easysoft software?
- Can I see which columns are available through the Easysoft software?
- 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:
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, 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'' ')
Using this method you can only get a list of the columns that belong to the table you specify in the TABLE_NAME WHERE clause. If you want to see 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