locked
MySQL to SQL Server error RRS feed

  • Question

  • I follow this link https://www.codeproject.com/articles/29106/migrate-mysql-to-microsoft-sql-server to create a linked server. After created I can see MySQL under the Linded Servers.

    When I query it useing:

                           SELECT *

                           FROM openquery(MYSQL, 'SELECT * FROM MysqlDB.TableName')

    I got error:

    Msg 7342, Level 16, State 1, Line 8

    An unexpected NULL value was returned for column "[MSDASQL].updated" from OLE DB provider "MSDASQL" for linked server "MySQL". This column cannot be NULL.

    How can I fix it?

    Thank you.

    Tuesday, November 21, 2017 7:31 PM

Answers

  • Hi, so I tried to replicated the similar/same error on my environment 

    And I created test db called mydb on MySQL and created table called table1 with column 'updated' datetime format

    And insert two values

    If I try to query this from my SQL Server 

    select *
    FROM openquery(linkedmysql, 'SELECT * FROM mydb.table1')

    I am getting 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.

    MySQL 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

    But if you use following query

    select *
    FROM openquery(linkedmysql, 'SELECT nullif( updated, '''') as date FROM mydb.table1')

    result is

    • Proposed as answer by Teige Gao Wednesday, November 22, 2017 1:27 AM
    • Marked as answer by MDXLaotu Wednesday, November 22, 2017 8:49 PM
    Tuesday, November 21, 2017 11:19 PM

All replies

  • HI,

    You can use some workaround using COALESCE or maybe NULLIF

    Using NULLIF function you can replace any nulls, instead of using * try putting something like NULLIF(updated,'')

    There is a post (with select into query), maybe can give you some idea

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/28cf69ca-53b8-42de-9660-4eff1aa96c7e/newbie-linked-mysql-openquery-results-in-an-unexpected-null-value-was-returned-for-column?forum=sqldataaccess



    • Edited by baraczof Tuesday, November 21, 2017 8:25 PM
    • Proposed as answer by AV111 Tuesday, November 21, 2017 9:05 PM
    • Unproposed as answer by AV111 Tuesday, November 21, 2017 9:50 PM
    Tuesday, November 21, 2017 8:11 PM
  • Thank you Baraczof. I tried this 

       Select COALESCE(CONVERT(date, updated), '1900-01-01')
       FROM openquery(MYSQL, 'SELECT updated FROM common.ms_users')

    I got the same error message:

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


    Tuesday, November 21, 2017 9:40 PM
  • I also tried 

    select isnull( updated, '1900-01-01')
    FROM openquery(MYSQL, 'SELECT updated FROM common.ms_users')

    and

    select nullif( updated, '')
    FROM openquery(MYSQL, 'SELECT updated FROM common.ms_users')

    All got the same error message.

    Tuesday, November 21, 2017 9:43 PM
  • The nullif/coalesce advice is not applicable. The problem is that first the MySQL provider reports a colunm in the table as non-NULLable, and then a NULL value comes back. SQL Server is really only the messegner - telling you that it has been cheated.

    The way to troubleshoort this would be to list the columns from the table explicitly (which is best practice anyway), and then remove columns until you can track down which is the culprit. If you are lucky, you don't need that column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 21, 2017 10:11 PM
  • Hi, so I tried to replicated the similar/same error on my environment 

    And I created test db called mydb on MySQL and created table called table1 with column 'updated' datetime format

    And insert two values

    If I try to query this from my SQL Server 

    select *
    FROM openquery(linkedmysql, 'SELECT * FROM mydb.table1')

    I am getting 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.

    MySQL 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

    But if you use following query

    select *
    FROM openquery(linkedmysql, 'SELECT nullif( updated, '''') as date FROM mydb.table1')

    result is

    • Proposed as answer by Teige Gao Wednesday, November 22, 2017 1:27 AM
    • Marked as answer by MDXLaotu Wednesday, November 22, 2017 8:49 PM
    Tuesday, November 21, 2017 11:19 PM
  • Thank you Baraczof, it works!
    Wednesday, November 22, 2017 8:49 PM