ODBC Driver for Salesforce: Updates not working

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
fglapu
Posts: 5
Joined: Mon 27 Aug 2018 16:42

ODBC Driver for Salesforce: Updates not working

Post by fglapu » Tue 11 Sep 2018 17:46

Hi. After I got the permissions and settings right, I could start to explore the capabilities of the ODBC driver for Salesforce.

SELECT queries are kinda slow but work.

However, UPDATEs always time out. Even a seemingly simple query runs for ~15 minutes till it fails:

Code: Select all

Update SFDC...Contact
SET TargetX_Email__AltEmail__c = Additional_Email__c
where X18_Dig_RecordID__c = '0036100000tBTAIAA4'
Error:
OLE DB provider "MSDASQL" for linked server "SFDC" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 73
The OLE DB provider "MSDASQL" for linked server "SFDC" could not UPDATE table "[SFDC]...[Contact]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
Our Contact object has 150,000+ records. Is this too much for the driver?
Or is there something wrong with my query?
Is there a document that outlines best practices how to use the driver properly?
Or is there a more performant alternative?

Thanks so much.

MaximG
Devart Team
Posts: 1141
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for Salesforce: Updates not working

Post by MaximG » Fri 14 Sep 2018 14:07

Try executing the query you want as follows :

Code: Select all

Update C
   Set C.TargetX_Email__AltEmail__c = C.Additional_Email__c
  From OPENQUERY([SFDC], 'Select TargetX_Email__AltEmail__c, Additional_Email__c From Contact Where X18_Dig_RecordID__c = ''0036100000tBTAIAA4''') C

fglapu
Posts: 5
Joined: Mon 27 Aug 2018 16:42

Re: ODBC Driver for Salesforce: Updates not working

Post by fglapu » Mon 17 Sep 2018 16:07

Instead of 15 minutes the updated query successfully updates the record in about ~15 seconds. Good enough. Thank you, Maxim!

MaximG
Devart Team
Posts: 1141
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for Salesforce: Updates not working

Post by MaximG » Tue 18 Sep 2018 05:14

We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our product usage.

Post Reply