none
sql server import using odbc driver and mysql database

    Question

  • im trying to import my mysql database into sql server. i use the odbc driver to access the odbc dns i have in windows.

    i am using the native client of sql server as the destinateion.

    both source and destinate are on my localhost.

    i get some errors regarding the datatype date. in my source it puts the value 23 as the datatype so i edit the mapping to be of type date.

    once i do this for all columns of type date in the database i hit continue and i get errors that look like this:

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The preview data could not be retrieved.

    ------------------------------
    ADDITIONAL INFORMATION:

    ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.20-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"district"' at line 1 (myodbc5w.dll)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    i do not know how to fix this. it comes up for all tables.

    Thursday, November 23, 2017 2:51 PM

All replies

  • Hi gconstanto,

    Whilst I do not understand what is value 23 you need to not to covert anything on the MySQL side, just ensure you can retrieve data from. MySQL ODBC driver should interrogate the source schema and correctly determine the datatypes on its own. E.g. the MySQL DATE is the ISO date format YYYY-MM-DD that will map into SQL Server DATE. So once you are able to read from MySQL you may need to convert the MySQL date format to conform to those in SQL Server using the Data Conversion Transformation.

    If that does not work please tell us what MySQL Date datatype is used.


    Arthur

    MyBlog


    Twitter

    Thursday, November 23, 2017 2:59 PM
    Moderator
  • TITLE: SQL Server Import and Export Wizard
    ------------------------------

    Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


    "branch" -> [dbo].[branch]:

              - The data type could not be assigned to the column "Established_Date" in "SQL Server Native Client 11.0".

    "company" -> [dbo].[company]:

              - The data type could not be assigned to the column "Established_Date" in "SQL Server Native Client 11.0".

    "identification_card" -> [dbo].[identification_card]:

              - The data type could not be assigned to the column "issue_date" in "SQL Server Native Client 11.0".
              - The data type could not be assigned to the column "expiry_date" in "SQL Server Native Client 11.0".

    "user" -> [dbo].[user]:

              - The data type could not be assigned to the column "dateCreated" in "SQL Server Native Client 11.0".
              - The data type could not be assigned to the column "dateLastPasswordChanged" in "SQL Server Native Client 11.0".


    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    established_date is of type date in mysql database.

    Thursday, November 23, 2017 3:05 PM
  • 23 is not a date value so not sure why you're trying to convert it to date.

    We're using ODBC DSNs for data transferring data from MySQL to SQLServer and date datatypes are working fine even without any conversions being performed


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 23, 2017 3:13 PM
  • Ah, now it became clear what is going on. You supposed to use the .net provider

    Please follow the steps in https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-mysql-data-source-sql-server-import-and-export-wizard


    Arthur

    MyBlog


    Twitter

    Thursday, November 23, 2017 3:15 PM
    Moderator
  • i follow the steps and get a new set of issues below:

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


    "address" -> "address":

              - The data type could not be assigned to the column "Line_1" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Line_2" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Geo_Exact" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Geo_General" in ".Net Framework Data Provider for MySQL".

    "address_type" -> "address_type":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "area" -> "area":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Area_Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Lo_Code" in ".Net Framework Data Provider for MySQL".

    "area_code" -> "area_code":

              - The data type could not be assigned to the column "Area_Code" in ".Net Framework Data Provider for MySQL".

    "branch" -> "branch":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Government_ID" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Tax_ID" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Email" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Homepage" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Established_Date" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Logo" in ".Net Framework Data Provider for MySQL".

    "company" -> "company":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Government_ID" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Tax_ID" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Email" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Homepage" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Established_Date" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Logo" in ".Net Framework Data Provider for MySQL".

    "contact" -> "contact":

              - The data type could not be assigned to the column "First_Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Last_Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Photo" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Marital_Status" in ".Net Framework Data Provider for MySQL".

    "contact_group" -> "contact_group":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "contact_salutation" -> "contact_salutation":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "country" -> "country":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Country_Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "country_dialing_code" -> "country_dialing_code":

              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".

    "district" -> "district":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "District_Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "LO_Code" in ".Net Framework Data Provider for MySQL".

    "identification_card" -> "identification_card":

              - The data type could not be assigned to the column "id_number_system_authority" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "id_number" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "issue_date" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "expiry_date" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "name_on_card" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "additional_info" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "scanned_picture" in ".Net Framework Data Provider for MySQL".

    "identification_type" -> "identification_type":

              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "sample_picture" in ".Net Framework Data Provider for MySQL".

    "job_position" -> "job_position":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "language" -> "language":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "map_address_system_group" -> "map_address_system_group":

              - The data type could not be assigned to the column "Notes" in ".Net Framework Data Provider for MySQL".

    "map_social_media_contacts" -> "map_social_media_contacts":

              - The data type could not be assigned to the column "Url" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Notes" in ".Net Framework Data Provider for MySQL".

    "map_telephone_system_group" -> "map_telephone_system_group":

              - The data type could not be assigned to the column "Notes" in ".Net Framework Data Provider for MySQL".

    "postal_code" -> "postal_code":

              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".

    "social_media" -> "social_media":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Url" in ".Net Framework Data Provider for MySQL".

    "street" -> "street":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".

    "system_group" -> "system_group":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "telephone" -> "telephone":

              - The data type could not be assigned to the column "Telephone" in ".Net Framework Data Provider for MySQL".

    "telephone_type" -> "telephone_type":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "town" -> "town":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Town_Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "LO_Code" in ".Net Framework Data Provider for MySQL".

    "user" -> "user":

              - The data type could not be assigned to the column "userName" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "dateCreated" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "dateLastPasswordChanged" in ".Net Framework Data Provider for MySQL".

    "zone" -> "zone":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".

    "zone_build_type" -> "zone_build_type":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".

    "zone_figures" -> "zone_figures":

              - The data type could not be assigned to the column "name_zone_build_type" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "max_build_percentage" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "max_floors" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "max_height" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "max_coverage_percentage" in ".Net Framework Data Provider for MySQL".

    "zone_simple_type" -> "zone_simple_type":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Icon" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".

    "zone_type" -> "zone_type":

              - The data type could not be assigned to the column "Name" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Code" in ".Net Framework Data Provider for MySQL".
              - The data type could not be assigned to the column "Description" in ".Net Framework Data Provider for MySQL".


    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Thursday, November 23, 2017 3:23 PM
  • so much to read :-)

    what are these (select only) data types?


    Arthur

    MyBlog


    Twitter

    Thursday, November 23, 2017 3:50 PM
    Moderator
  • Hi gconstanto,

    -->>The data type could not be assigned to the column "Established_Date" in "SQL Server Native Client 11.0".

    You are trying to assign something to a field defined as Established_Date that is not a date value. Why is the 23 a date data type? Please check these fields and ensures the types match those in your SQL Server Database.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 24, 2017 7:46 AM
    Moderator