SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column

Error:

Msg 7342, Level 16, State 1, Line 8

An unexpected NULL value was returned for column “[MSDASQL].column” from OLE DB provider “MSDASQL” for linked server “LinkedMySQL”. This column cannot be NULL

At the same time when I started writing this blog I also became active on MSDN forum, where I am trying to help SQL users to resolve theirs database issues.

I just wanted to share with you one interesting issue appeared today (Msg 7342, Level 16, State 1, Line 8, An unexpected NULL value was returned for column)

An unexpected NULL value was returned for column “[MSDASQL].column” from OLE DB provider “MSDASQL” for linked server “LinkedMySQL”. This column cannot be NULL

I suggested following solution:

 

I used my environment to replicate similar/same error

One SQL Server 2012 and one linked MySQL server.

 

On MySQL server I created DB called “MyDB” and created table called “table1” with one column named “updated”.  Then I inserted two rows

 

If I try to query  this db with openquery  from my SQL Server 2012 I am getting an error

 

Msg 7342, Level 16, State 1, Line 1
An unexpected NULL value was returned for column “[MSDASQL].updated” from OLE DB provider “MSDASQL” for linked server “linkedmysql”. This column cannot be NULL

 

Problem is that MySQL server accepts datetime values in the format of “0000-00-00”, but that will be equivalent to NULL date value in SQL server. And I will get an error.

 

Nevertheless, if I try to use NULLIF function within openquery

 

I get following results

 

Bear in mind that this is just workaround solution.

 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *