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: 1822
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: 1822
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.

Sanjay5103
Posts: 1
Joined: Mon 05 Apr 2021 10:45

Re: ODBC Driver for Salesforce: Updates not working

Post by Sanjay5103 » Wed 07 Apr 2021 07:54

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.

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

Re: ODBC Driver for Salesforce: Updates not working

Post by MaximG » Thu 13 May 2021 08:44

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

Post Reply