Devart Forums

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.

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

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

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

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

jlgervais

- Created a new local admin user and set it up to run MSSQLSERVER (restarted)
- used sa to connect
- Allow Inprocess was disabled
I Still have the same Error (The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311))

Edit : Tested in two environments :
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: )

Exact same result on both

jlgervais

It worked, i was using *
INSERT OPENQUERY ([SFCxp], 'SELECT * FROM TestODBC__c') VALUES ('TEST')
instead of naming each field when i tried using openquery
INSERT OPENQUERY ([SFCxp], 'SELECT Name FROM TestODBC__c') VALUES ('TEST')
Thanks a lot

jlgervais

- I Created a linked server (SFCxp)
- I Created a test table "TestODBC" it has one user column "Name"

Code: Select all

SELECT * FROM [SFCxp]...[TestODBC__c]

-- Works fine

Code: Select all

UPDATE [SFCxp]...[TestODBC__c]  SET Name = 'Update Test'

-- Works fine

Problem is, when i try to insert a row

Code: Select all

INSERT INTO [SFCxp]...[TestODBC__c]  (Name) VALUES ('TEST')

i get this error message :
OLE DB provider "MSDASQL" for linked server "SFCxp" returned message "[Devart][ODBC][Salesforce]Access violation at address 000000000063AE07 in module 'DevartODBCSalesforce.dll'. Write of address 00000000000000BA".
Msg 7343, Level 16, State 2, Line 4
The OLE DB provider "MSDASQL" for linked server "SFCxp" could not INSERT INTO table "[SFCxp]...[TestODBC__c]".

Inserting thru MSAccess works, with the same DSN

Anyone knows the secret of inserting a row in a salesforce table ?

Devart Forums

Search found 8 matches

Re: Salesforce ODBC Update Problem

Re: Salesforce ODBC Update Problem

Re: Salesforce ODBC Update Problem

Re: Salesforce ODBC Update Problem

Salesforce ODBC Update Problem

Re: How to add Linked Server in SSMS?

Re: Salesforce ODBC INSERT thru linked server in SSMS

Salesforce ODBC INSERT thru linked server in SSMS