Mysql – NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard

migrationMySQLmysql-workbench

My issue is similar to this one but and I have tried what has been suggested but I still get the error. I have set the target column from VARCHAR(5120) to TEXT (tried LONGTEXT too) but the issue still keeps appearing of those exact columns. I even went into the schema and checked and it says they are TEXT. Am I getting this issue because the source column is VARCHAR(5120)? If so, is there any way to solve this. I simply want to use this migrated database as a local development copy with proper data.

The error message is as following:

ERROR: gruppergister.Endring:SQLExecDirect(SELECT [id], CAST([endretFra] as NVARCHAR(8000)) as [endretFra], CAST([endretTil] as NVARCHAR(8000)) as [endretTil], CAST([endringskilde] as NVARCHAR(255)) as [endringskilde], CAST([sistEndretBruker] as NVARCHAR(255)) as [sistEndretBruker], CAST([sistEndretFulltNavn] as NVARCHAR(255)) as [sistEndretFulltNavn], [sistEndretTidspunkt], CAST([felt] as NVARCHAR(255)) as [felt], CAST([handling] as NVARCHAR(255)) as [handling], [aliste_id], [utvalg_id] FROM [gruppergister].[dbo].[Endring]):
42000:1131:[Microsoft][ODBC SQL Server Driver][SQL Server]
The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000).

It occurs during the "Bulk Data Transfer"

Best Answer

You are trying to execute this statement on the SQL Server side:

SELECT  [id],
        CAST([endretFra] AS NVARCHAR(8000)) AS [endretFra],
        CAST([endretTil] AS NVARCHAR(8000)) AS [endretTil],
        CAST([endringskilde] AS NVARCHAR(255)) AS [endringskilde],
        CAST([sistEndretBruker] AS NVARCHAR(255)) AS [sistEndretBruker],
        CAST([sistEndretFulltNavn] AS NVARCHAR(255)) AS [sistEndretFulltNavn],
        [sistEndretTidspunkt],
        CAST([felt] AS NVARCHAR(255)) AS [felt],
        CAST([handling] AS NVARCHAR(255)) AS [handling],
        [aliste_id],
        [utvalg_id]
FROM    [gruppergister].[dbo].[Endring]

That statement tries to convert two columns to the NVARCHAR data type with a length of 8000. However,the maximum allowed length of NVARCHAR in SQL Server is 4000. So, whatever tool you are using is producing invalid SQL Server syntax.

As a side note, if you are trying to create a development environment for a SQL Server hosted database, you probably should use SQL Server. The syntax for some statements is quite different and using the from DBMS can only cause confusion and grief.

You can install a trial version of SQL Server for free, if your project is less than 180 days, or you can buy the developer edition for about $50 (e.g. here). If your database is less than 4GB and does not use advanced features, you could also go with the free SQL Server Express Edition.

Related Question