'SSMA Column "column" does not allow DBNull. Value

I'm right now trying to migrate a Mysql db to Sql Server, I found in some forums that they recommend to use SSMA (SQL Sever Migration Assistant) but when I try to migrate a table which have datetime it returns the error "Column does not allow DBNull" only migrating less than 15%. The name of the databases are the same (lowercases), also I tried changing the Parallel data migration mode to custom, Zero Date columns and I haven't found a solution. My SQL server database is 2012

Thanks in advance.

mysqlsql-server


Solution 1:[1]

You are getting this error because the target table in SQL Server has a column defined with the NOT NULL constraint, but the source table in MySQL contains nulls in that column. You need to either fix your MySQL data to eliminate nulls or change the table definition in SQL Server to allow nulls (provided that the column is not part of a primary key, where nulls are not allowed)

Solution 2:[2]

Here is a screenshot of how the tables are structured - Mysql

CREATE TABLE users (
user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
last_login datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
first_name varchar(255) NOT NULL DEFAULT '',
last_name varchar(255) NOT NULL DEFAULT '',
user varchar(255) NOT NULL DEFAULT '',
pass varchar(32) NOT NULL DEFAULT '',
is_deleted tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (user_id),
UNIQUE KEY idx_userlogin (user,pass)
) ENGINE=MyISAM AUTO_INCREMENT=49018 DEFAULT CHARSET=latin1

  • SQL Server

CREATE TABLE [test].[users]
( [user_id] bigint IDENTITY(49018, 1) NOT NULL,
[create_date] datetime2(0) NOT NULL,
[last_login] datetime2(0) NOT NULL,
[first_name] varchar(255) NOT NULL,
[last_name] varchar(255) NOT NULL,
[user] varchar(255) NOT NULL,
[pass] varchar(32) NOT NULL,
[is_deleted] tinyint NOT NULL,
) WITH (DATA_COMPRESSION = NONE)

Regards.

Solution 3:[3]

It seems that SQL Server treats the MySQL default value of '0000-00-00 00:00:00' as NULL.

I had to update all of the data in MySQL to a value of '1970-01-01 00:00:00' to get SSMA to work.

e.g.

UPDATE <table> SET <column>='1970-01-01 00:00:00' WHERE <column>='0000-00-00 00:00:00';

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user1443098
Solution 2 Sokidraken
Solution 3 Andy Nugent

Related Questions