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

Posted by in MySQL, SQL Server, T-SQL on Nov 22nd, 2017 | 2 comments

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:

2 Responses to “ “SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column”

  1. lennard says:
    July 8, 2018 at 4:52 pm

    Thank You for this.

    Reply

Leave a Reply

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