0

My Sql server SQL like this:

update mysqlEmp
set first_name=FirstName
FROM OPENQUERY(NTEMYSQL,'select * from data_source_db.employees') mysqlEmp 
INNER JOIN Employee on Employee.ID=mysqlEmp.id_number
WHERE CONVERT(date,Employee.Editdate) between '2018-09-19' AND '2018-09-26'
  AND (mysqlEmp.first_name!=Employee.FirstName)
  and employee.id='A0002779'

It will show errer:

OLE DB provider "MSDASQL" for linked server "NTEMYSQL" 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 3 The OLE DB provider "MSDASQL" for linked server "NTEMYSQL" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

If I select the first_name using OPENQUERY (in sql server)

select first_name from OPENQUERY(NTEMYSQL,N'select * from 
data_source_db.employees') mysqlEmp 
where id_number='A0002779'

result : PHILIP NI?O

If I select the first_name in Mysql

select first_name from data_source_db.employees where id_number ='A0002779'

result :enter image description here

I think the Special characters enter image description here make my sql update errer

--Sql Server : column "FirstName" Type: nvarchar

--My sql: column "first_name" Type: utf8mb4_unicode_ci

How do I solve this problem?

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Victor Lin
  • 41
  • 1
  • 11
  • Those aren't special characters. It's just Unicode. `?` means the query tried to read *Unicode* as if it were ASCII. It's the *provider's* job to translate the character sets and codepages. When it fails, unrecognized characters are replaced by `?`. How was the linked server configured? Which provider did you use? Does it have settings that controll character set translation? MySQL has an [ANSI ODBC driver](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation.html) as well, which obviously can't handle Unicode – Panagiotis Kanavos Sep 27 '18 at 09:18
  • BTW your query reads *everything* from the MySQL database each time you run it, then tries to join with `Employee` without benefit of indexes. You could improve performance a *lot* by storing the data into a local table with indexes on the `id_number` and `first_name` tables. Another way to greatly improve performance is to load only the columns you need. Right now you are loading all columns but only using 2 – Panagiotis Kanavos Sep 27 '18 at 09:25
  • Can you get the hex of the column? `Ñ` is hex `D1` in latin1, hex `C391` in utf8, `U+00D1` in Unicode, etc. – Rick James Oct 01 '18 at 18:05
  • See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Oct 01 '18 at 18:06

0 Answers0