I’ve been trying to fix a data conversion error for several hours and finally figured out the issue so I’m going to document it here in case anyone else ever has to deal with it.
First off some background:
- I’m migrating data from Oracle 11 to SQL Server 2012
- I’m running Oracle in a Virtualbox VM (I’ll cover that in my next post)
- I’m using Navicat Premium to do the migration in VMWare on my MacBook Pro. Running it in Windows 10 seems faster and seems also to give better error messages. The data transfer seems to have more options too.
- The error message I was getting was:
[Msg] [Dtf] Start transfer to Target Server: [dbo].[ACQUISITIONS] [Err] [Dtf] 42000 – [SQL Server]Unclosed quotation mark after the character string ”. 42000 – [SQL Server]Incorrect syntax near ”.
The bummer is that when running the data transfer with the default settings I’d get the last 5 records that the transfer tried to insert. None of those were the offending record so that information is more or less useless.
My table has 1,500,000+ records in there and the IDs aren’t sequential so I couldn’t try to find a record before or after the ones in the error message.
Eventually I unchecked the “Use Transaction” and “Use Extended Insert Statements” boxes in the “Advanced” tab of the data transfer dialogue box. That along with unchecking the “Continue on error” box made the inserts stop on the problematic record.
But that only helped me find the bad record. Next up was figuring out which field or fields were causing the issue. The table had 38 fields so it took some researching.
After closely inspecting each field I finally realized two fields had some invisible special characters in them. I couldn’t actually SEE the characters in Windows at all.
Here’s what I’d see in OS X Navicat Premium:
If I moused into the field value box I’d see this:
And here’s what I’d see in TextMate. Only TextMate truly showed the invisible characters. Sublime and Atom didn’t.
So now that I know what’s causing the issue I need to figure out how to fix it. The first thing I wanted to do was be able to query Oracle and find the records with the <NUL> in them. After a lot of Googling I found an explanation of the null character and some information on how to use unicode strings in a query in Oracle.
After some experimenting I finally came up with the following query which works for me:
SELECT * FROM TABLE_NAME WHERE FIRST_NAME LIKE UNISTR(‘\0000%’) ORDER BY TABLE_NAME.ID
After that I was able to run some updates and then finally get my data migrated.