I'm trying to migrate few tables from SQL Server to MySQL using MySQL Workbench migration wizard. All work fine for structure migrations but when I go to the data migration section it throws an error for one table:
ERROR:
dbo
.Documents
:SQLExecDirect(SELECT [DocumentID],
[CategoryID], CAST([DocumentName] as NVARCHAR(255)) as [DocumentName],
[Active], [NavigatorID], CAST([DocumentText] as NTEXT) as
[DocumentText], [UseSubtitle], CAST([DocumentSubtitle] as
NVARCHAR(255)) as [DocumentSubtitle], CAST([DocumentPlainText] as
NTEXT) as [DocumentPlainText], [DocumentType], CAST([DocumentLink] as
NVARCHAR(255)) as [DocumentLink], [Sitemap], CAST([SubtitleImage] as
NVARCHAR(255)) as [SubtitleImage], CAST([MetaTags] as NVARCHAR(8000))
as [MetaTags], CAST([MetaDescription] as NVARCHAR(8000)) as
[MetaDescription], [AccessLevel] FROM [ctool_test].[dbo].[Documents]):
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).2131:[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).
Based on that what I can understand it limits columns with nvarchar
data to max size of 4000 when MySQL can handle 65535.
Any clue how I can get this to work?
Thanks
Best Answer
Well, since you have data currently stored in SQL Server, and it's already in an
NVARCHAR
column, then either it's anNVARCHAR <= 4000
(in which case you can't lose any data, and should just change all instances ofNVARCHAR(8000)
toNVARCHAR(4000)
), or it's anNVARCHAR(MAX)
column, in which case you change all instances ofNVARCHAR(8000)
toNVARCHAR(MAX)
. Or just leave out thoseCASTs
- do you really need them?As an aside, you should probably change
as NTEXT
toas NVARCHAR(MAX)
as well.