SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column
Posted by Barac in MySQL, SQL Server, T-SQL on Nov 22nd, 2017 | 2 commentsError:
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)
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:
- SQL Server / Upgrading the Secondary Server Instances (STANDBY)
- SQL Server / Extended Events / Finding Deadlocks
- SQL Server / BACPAC / Could not extract package from specified database – The wait operation timed out
- SQL Server / Query Optimization / Merge Join Operator / Sort Operator
- Powershell / Providers / Get-ChildItem : Invalid Path
2 Responses to “ “SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column”
Leave a Reply to Barac Cancel reply
About Me
My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.
read moreAuckland SQL Group
MSDN PROFILE
SQL SERVER UPDATES
- Microsoft SQL Server 2022 Updates / Release Candidate (RC 1) Evaluation Edition (16.0.950.9)
- Microsoft SQL Server 2019 Updates / BETA (15.0.1000.34) / RTM (15.0.2000.5)
- Microsoft SQL Server 2017 Updates / RTM (14.0.1000.169)
- Microsoft SQL Server 2016 Updates / RTM (13.0.1601.5) / SP1 (13.0.4001.0 or 13.1.4001.0) / SP2 (13.0.5026.0 or 13.2.5026.0) / SP3 (13.0.6300.2 or 13.3.6300.2)
- Microsoft SQL Server 2014 Updates / RTM (12.0.2000.0) / SP1 (12.0.4100.1 or 12.1.4100.1) / SP2 (12.0.5000.0 or 12.2.5000.0) / SP3 (12.0.6024.0 or 12.3.6024.0)
- Microsoft SQL Server 2012 Updates / RTM (11.00.2100) / SP1 (11.0.3000.0 or 11.1.3000.0) / SP2 (11.0.5058.0 or 11.2.5058.0) / SP3 (11.0.6020.0 or 11.3.6020.0) / SP4 (11.0.7001.0 or 11.4.7001.0)
Thank You for this.
Sorry I missed this comment.
You are welcome.