Importing table from MySQL into MSSQL error with dates being 0000-00-00

Transact-SQL
1

I need to import some MySQL tables into my MSSQL 2019 server and keep getting null errors. "An unexpected NULL value was returned for column deployment_date"
Here is the snip from the stored procedure.

=================================
CREATE TABLE dbo.RAP_project (
id int NOT NULL ,
address text ,
network int DEFAULT '0',
loc int DEFAULT '0',
analyste int DEFAULT '0',
csr_date date NULL,
deployment_date date NULL,

=================================
I've also tried deployment_date VARCHAR(20) NULL,

Thanks

Pete

PS forgot to add the actual insert from a linked server.

SELECT * INTO dbo.RAP_project
from [MYSQL_progmgt]...[dbo.project]

2

I just created another stored procedure that just opens the table and read it!

SELECT * FROM OPENQUERY([MYSQL_progmgt] 'SELECT * FROM dbo.project')
and I get the same unexpected NULL error.

Pete

3

something looks amiss here

SELECT * 
  FROM OPENQUERY([MYSQL_progmgt], 'SELECT * FROM dbo.project')
4

Sorry as I typed out the wrong snippet of code.

The following works with all my other linked server tables , but not the one with the dates.

Select * from [MYSQL_progmgt]...[dbo.project]

Peter

5

What is

[MYSQL_progmgt]...[dbo.project]

A linked server?

6

Yes the linked server is [MYSQL_progmgt] and works well with the other tables on the linked server just by switching the table name.[MYSQL_progmgt]...[dbo.loc] etc for 6 others

Pete

7

An unexpected NULL value was returned for column "[MYSQL_progmgt]...[dbo.project].deployment_date" from OLE DB provider "MSDASQL" for linked server "MYSQL_progmgt". This column cannot be NULL. Even when I check the design it shows the field allows NULLS, so I really don't get it unless this is one of those things where so obscure seemingly unreated option whatever is causing this. All other tables work perfectly.

Pete

8

When you do what? You are showing an error without the code causing the error.

SELECT * INTO dbo.RAP_project
from [MYSQL_progmgt]...[dbo.project]

Is this it here?

What do you get when you simply do

SELECT * 
from [MYSQL_progmgt]...[dbo.project]
9

I get the same error.
An unexpected NULL value was returned for column "[MYSQL_progmgt]...[bire.projet].deployment_date" from OLE DB provider "MSDASQL" for linked server "MYSQL_progmgt". This column cannot be NULL.
All other table I try with the same code work perfectly.

Peter

10

Why would a query for

SELECT *
from [MYSQL_progmgt]...[dbo.project]

Give an error on a different table

MYSQL_progmgt]...[bire.projet].deployment_date"

What happens if you go to the linked server open db and click on project table right click and choose select

11

MYSQL_progmgt]...[bire.projet].deployment_date" is the field name where the error occurs.

apparently 0000-00-00 is ok for a default value in MySQL but SQL server see the field as NOT NULL but also interprets 0000-00-00 as a NULL, which contradicts the NOT NULL. There doesn't seem to be a work around. the problem is due to the default value of 0000-00-00 being assigned to the field.

Pete

12

no, my question to you is if you do a simple query as follows without trying to insert it into sql server, do you get an error?

select * 
  from [MYSQL_progmgt]...[bire.projet].deployment_date

also what locale is this date format 0000-00-00

13

How about:

SELECT * 
FROM OPENQUERY
(
[MYSQL_progmgt],
'SELECT id
	,address
	,network
	,loc
	,analyste
	,IF(csr_date = ''0000-00-00'', ''1900-01-01'', csr_date) AS csr_date
	,IF(deployment_date = ''0000-00-00'', ''1900-01-01'', deployment_date) AS deployment_date
FROM project'
);
1 Like
14

or using a sample

declare @booze table(dt varchar(10))

insert into @booze
select '0000-00-00' union
select '2022-02-25'

create table #wine(dt varchar(10), dt2 date)

insert into #wine
select dt as dt, 
       case 
	      when try_convert(date, dt, 23 ) is null then null
		  else try_convert(date, dt, 23 )
		  end as dt2 from @booze

select * from #wine

drop table #wine
15

WHen I execute select * from [MYSQL_progmgt]...[bire.projet].deployment_date

I get the same error.
An unexpected NULL value was returned for column "[MYSQL_progmgt]...[bire.projet].deployment_date" from OLE DB provider "MSDASQL" for linked server "MYSQL_progmgt". This column cannot be NULL.

If I try IF(csr_date = ''0000-00-00'', ''1900-01-01'', csr_date) AS csr_date
I get Incorrect syntax near '.'.

Peter

16

A colleague suggested I do it like this and it works.
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @TSQL_SELECT nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MYSQL_ECHEANCIER'
SET @TSQL_SELECT = 'SELECT * '
SET @OPENQUERY = ' FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT nullif( project.deployment_date, ''''0000-00-00'''') as deployment_date,
nullif( project.csr_date, ''''0000-00-00'''') as csr_date FROM dbo.project '')'
EXEC (@TSQL_SELECT+@OPENQUERY+@TSQL)

I can't see why the quadruple quotes makes it work in this configuration as quad quotes didn't work in my earlier trials.

As long as it works.

Thanks for your help.

Pete

17

so this is confusing, is deployment_date a table in mysql?

18

No , it's a field in the table project. nullif( project.deployment_date

Peter

19

So this query looks odd

select * from [MYSQL_progmgt]...[bire.projet].deployment_date

you are selecting from a column instead of a table? You cant do that. if you want to select just deployment_date you need to do

select deployment_date from [MYSQL_progmgt]...[bire.projet]
20

Yes very sorry about that I should have double checked the code before submitting ,
select deployment_date from [MYSQL_progmgt]...[bire.projet] is the correct code.

Pete