I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column 'Address1'

As I am trying to automate data migrations that will happen regularly, I need to find a solution I can put into a script. I am using wbcopytables.exe at the moment, but I do not necessarily need to.

In just trying to find an appropriate solution to later implement in the script, I have tried running the below in SQL Server to fix the source data:

UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\xA0', ' ')


UPDATE [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
SET Address1 = REPLACE(Address1 COLLATE Latin1_General_CI_AS, N'\u00A0', ' ')

but that is not fixing the source data, and my Google-Fu is failing me. Thoughts?

  • What charset are you using for the conversion? For the target table? – Rick James Jun 7 '16 at 15:13

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data:

select * 
from [ForgeDB].[Snapshot].[SnapshotPersonEmployee]
where charindex(0xA0, cast(Address1 as varbinary(8000))) > 0;

Check this answer: https://dba.stackexchange.com/a/37355/55596

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.