Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What are the source columns (NVARCHAR(4000) or NVARCHAR(MAX))? How are you building that SELECT statement? –  Aaron Bertrand Aug 22 '13 at 19:29
    
Source column has text data and is set as NVARCHAR(8000). Select statement is done by MySQL Workbench so I even don't need to touch queries (but I can save them for command line execution if needed) –  JackTheKnife Aug 22 '13 at 19:36
    
There is no such thing as NVARCHAR(8000). Where are you seeing this? –  Aaron Bertrand Aug 22 '13 at 19:38
    
Through MySQL Workbench migration wizard - Create Schemata step. –  JackTheKnife Aug 22 '13 at 19:43
    
Well MySQL Workbench does not have a very good grasp of SQL Server data types. –  Aaron Bertrand Aug 22 '13 at 19:44
show 5 more comments

1 Answer

up vote 1 down vote accepted

Well, since you have data currently stored in SQL Server, and it's already in an NVARCHAR column, then either it's an NVARCHAR <= 4000 (in which case you can't lose any data, and should just change all instances of NVARCHAR(8000) to NVARCHAR(4000)), or it's an NVARCHAR(MAX) column, in which case you change all instances of NVARCHAR(8000) to NVARCHAR(MAX). Or just leave out those CASTs - do you really need them?

As an aside, you should probably change as NTEXT to as NVARCHAR(MAX) as well.

share|improve this answer
    
Well, let me try that :) Thanks! –  JackTheKnife Aug 22 '13 at 19:38
    
OK, got this to work following all answers above. –  JackTheKnife Aug 22 '13 at 19:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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