Salesforce ODBC Update Problem

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Salesforce ODBC Update Problem

Post by jlgervais » Mon 29 Jan 2018 16:42

Code: Select all

--Working
SELECT strLicence__c FROM [SFCxp]...[Account] WHERE strLicence__c = '45815'
UPDATE [SFCxp]...[Account] SET strLicence__c = '45815' WHERE strLicence__c = '45815'
SELECT strLicence__c FROM [SFCxp]...[Account] WHERE strLicence__c = 'C32012'
--Not Working
UPDATE [SFCxp]...[Account] SET strLicence__c = 'C32012' WHERE strLicence__c = 'C32012'
Error Message
OLE DB provider "MSDASQL" for linked server "SFCxp" 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 160
The OLE DB provider "MSDASQL" for linked server "SFCxp" could not UPDATE table "[SFCxp]...[Account]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

Yes, strLicence__c is a Text(20) Custom Field
It used to work

jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Re: Salesforce ODBC Update Problem

Post by jlgervais » Tue 30 Jan 2018 16:45

Forgot to mention that strLicence__c is defined as an "external ID"

Testing a simple query
I can update this row sucessfully , it has a numeric "strLicence__c" number (strLicence__c = '45815')

Code: Select all

UPDATE [SFCxp]...[Account] SET BillingCountry = BillingCountry WHERE Id = '0014100000PShz4AAD'


But i can NOT update this row this row has an alphanumeric "strLicence__c" (strLicence__c = 'C32012')

Code: Select all

UPDATE [SFCxp]...[Account] SET BillingCountry = BillingCountry WHERE Id = '0014100000QHZ3MAAX'

I get the infamous
OLE DB provider "MSDASQL" for linked server "SFCxp" 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 125
The OLE DB provider "MSDASQL" for linked server "SFCxp" could not UPDATE table "[SFCxp]...[Account]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
Even if "strLicence__c" field is not considered anywhere in the query

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

Re: Salesforce ODBC Update Problem

Post by MaximG » Wed 31 Jan 2018 16:24

Thanks for the information. Unfortunately, we could not reproduce the problem according to your description. Please provide additional information. Do we correctly understand that the "strLicence__c" field is created as "External Lookup Relationship"? Do we understand correctly that the query

Code: Select all

UPDATE [SFCxp] ... [Account] SET BillingCountry = BillingCountry WHERE Id = '0014100000QHZ3MAAX' 
causes the error, despite the fact that it does not affect the "strLicence__c" field?

jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Re: Salesforce ODBC Update Problem

Post by jlgervais » Wed 31 Jan 2018 16:26

Yes it does not work

strLicence__c is only marked as "External ID" there's no lookup on anything, it is basically an information
I unchecked the "ExternalId" box, it still doesn't work
I am not sure it concerns strLicence__c field anymore, i've updated other record that has 'Cxxxxx' value

It is not a "rights" problem, i can, with the exact same account my ODBC driver uses, update manually an entry that cause the error

jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Re: Salesforce ODBC Update Problem

Post by jlgervais » Thu 01 Feb 2018 19:09

Even if i read that using OPENQUERY was not a good practice, i tried the following
Using an Id that was not working, this query is still not working

Code: Select all

UPDATE OPENQUERY(SFCxp,'SELECT * FROM Account') SET BillingCountry = BillingCountry WHERE Id = '0014100000VxiFDAAZ'
Specifying the fields is working

Code: Select all

UPDATE OPENQUERY(SFCxp,'SELECT Id,BillingCountry,strLicence__c FROM Account') SET BillingCountry = BillingCountry WHERE Id = '0014100000VxiFDAAZ'
Since i cannot specify the fields querying my linked server directly like this

Code: Select all

UPDATE [SFCxp]...[Account] SET BillingCountry = BillingCountry WHERE Id = '0014100000VHT7eAAH'
I am considering changing huge parts of my code

jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Re: Salesforce ODBC Update Problem

Post by jlgervais » Thu 01 Feb 2018 20:04

Two "Text Area 255" Fields are causing the error
when these fields contain special characters like é,ê,',à (French Characters)
Salesforce uses SQL_Latin1_General_CP1_CI_AS and my local Database that runs the code uses French_CI_AI, When i read the data, there is a conversion I can't control yet, but the problem is right there.

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

Re: Salesforce ODBC Update Problem

Post by MaximG » Fri 02 Feb 2018 15:47

Thank you for the clarification. We will test the operation of our driver when using different code pages according to your description. For this, we need some time. We will inform you about the results after the end of testing

ReCorn066
Posts: 1
Joined: Fri 26 Jul 2019 11:21
Location: The Hague, The Netherlands
Contact:

Re: Salesforce ODBC Update Problem

Post by ReCorn066 » Fri 26 Jul 2019 11:45

Hi, I'm having similar problem with this statement:
update [SalesForce_UAT]...[Contact] set Salutation = null where Salutation = 'Mr.'

I have tried changing the linked server property "Collation Name" = "SQL_Latin1_General_CP1_CI_AS" , but same result
Also the property "Use Remote Collation" = "False" (previous setting = True), but same result
Also the property "Collation Compatible" = "True" (previous setting = False), but same result

Is there any way how to resolve these errors?
Kind regards, Ronald

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

Re: Salesforce ODBC Update Problem

Post by MaximG » Tue 30 Jul 2019 16:42

Have you followed the above execution instruction for the UPDATE OPENQUERY statement?

Post Reply