SQLTeam.com | Weblogs | Forums

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