Page 1 of 1
Salesforce ODBC Update Problem
Posted: Mon 29 Jan 2018 16:42
by jlgervais
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
Re: Salesforce ODBC Update Problem
Posted: Tue 30 Jan 2018 16:45
by jlgervais
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
Re: Salesforce ODBC Update Problem
Posted: Wed 31 Jan 2018 16:24
by MaximG
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?
Re: Salesforce ODBC Update Problem
Posted: Wed 31 Jan 2018 16:26
by jlgervais
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
Re: Salesforce ODBC Update Problem
Posted: Thu 01 Feb 2018 19:09
by jlgervais
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
Re: Salesforce ODBC Update Problem
Posted: Thu 01 Feb 2018 20:04
by jlgervais
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.
Re: Salesforce ODBC Update Problem
Posted: Fri 02 Feb 2018 15:47
by MaximG
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
Re: Salesforce ODBC Update Problem
Posted: Fri 26 Jul 2019 11:45
by ReCorn066
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
Re: Salesforce ODBC Update Problem
Posted: Tue 30 Jul 2019 16:42
by MaximG
Have you followed the above execution instruction for the UPDATE OPENQUERY statement?