Page 1 of 1

ODBC Driver for Salesforce: Updates not working

Posted: Tue 11 Sep 2018 17:46
by fglapu
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.

Re: ODBC Driver for Salesforce: Updates not working

Posted: Fri 14 Sep 2018 14:07
by MaximG
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

Re: ODBC Driver for Salesforce: Updates not working

Posted: Mon 17 Sep 2018 16:07
by fglapu
Instead of 15 minutes the updated query successfully updates the record in about ~15 seconds. Good enough. Thank you, Maxim!

Re: ODBC Driver for Salesforce: Updates not working

Posted: Tue 18 Sep 2018 05:14
by MaximG
We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our product usage.

Re: ODBC Driver for Salesforce: Updates not working

Posted: Wed 07 Apr 2021 07:54
by Sanjay5103
Update C
Set C.Name = 'S'
From OPENQUERY([LOCALHOST:27017], 'Select Name From [test].[EMP] Where row_id = 3') C

Hello, I tried to update the table using above query.
[LOCALHOST:27017] = Linked server name for MongoDB
[EMP] = Table Name (also checked by removing [] brackets)
[test] = DB Name in MongoDB (also checked by removing [] brackets)
Name = Column which needs to be updated

I am getting below error:

OLE DB provider "MSDASQL" for linked server "LOCALHOST:27017" returned message "Data provider or other service returned an E_FAIL status.".
Msg 7343, Level 16, State 4, Line 10
The OLE DB provider "MSDASQL" for linked server "LOCALHOST:27017" could not UPDATE table "[MSDASQL]".

could you help on this.

Re: ODBC Driver for Salesforce: Updates not working

Posted: Thu 13 May 2021 08:44
by MaximG
We've reproduced the issue and fixed it. The fix will be included in the next build of our product. As a workaround, we can send you a night build including the required changes. For this provide us with your license number. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html