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"

  • 2
    >"I simply want to use this migrated database as a local development copy" If that's the case, look into Sql Server Express Edition (free) or Developer Edition (not free, but more features and only like $50). – Joel Coehoorn Mar 20 '14 at 20:41

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],
        CAST([felt] AS NVARCHAR(255)) AS [felt],
        CAST([handling] AS NVARCHAR(255)) AS [handling],
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.

  • Thanks for the reply. The reason I am using the tool is because it can convert MSSQL (which is the original database) to MySQL which I was thinking of running locally. The tool comes with the MySQL installation. I tried changing the fields away from VARCHAR(8000) and into TEXT fields, but it still produces the same error. Perhaps I am just going to go with MSSQL locally as well. – Wobbley Mar 21 '14 at 6:27
  • Give the upcoming MySQL Workbench 6.1 RC a try (mysqlworkbench.org). This bug might have been solved already. – Mike Lischke Mar 21 '14 at 8:09

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.