Copy View from MS SQL Server Database to a MySQL table

1

I know there's several methods to achieve what I want but I've gone down the rabbit hole and I need help with the following:

I want to copy a view called AllOrdersData from a Microsoft SQL Server Database to a MySQL Database table. The end goal is to eventually automate this process on a hourly interval but let's just get it work for now.

I would like to copy the data to a table on MySQL Database called AllData but if I have to create a new table that's fine as well.

I know I can connect to the SQL database directly but I need to do it this way.

I'm trying to do this with Microsoft SQL Server Management Studio but if there's a better application, please let me know. I can connect to the MySQL Database and create the table but then I get an error (See screenshot below).

Right now, I'm creating the SQL statement but I've tried the auto generate statement and I still get errors. Please note, I'm using the same column names on the AllOrdersData view. The only difference between the two is the column types.

CREATE TABLE AllData2 (
    OrderNumber             int(9)          NOT NULL    auto_increment
   ,OHeader_Status          varchar(255)    NOT NULL
   ,OrderDate               varchar(255)    NOT NULL
   ,ShipVia                 varchar(255)    NOT NULL
   ,ODetail_ID              varchar(255)    NOT NULL
   ,Item                    varchar(255)    NOT NULL
   ,ODetail_ItemDescription varchar(255)    NOT NULL
   ,Serial                  varchar(255)    NOT NULL
   ,ShipToEntity            varchar(255)    NOT NULL
   ,ShipToName              varchar(255)    NOT NULL
   ,DetailShipDate          varchar(255)    NOT NULL
   ,NET_ServiceDate         varchar(255)    NOT NULL
   ,NET_JobNumber           varchar(255)    NOT NULL
   ,NET_LastImportDate      varchar(255)    NOT NULL
   ,NET_MachineStatus       varchar(255)    NOT NULL
   ,NET_EffectiveDate       varchar(255)    NOT NULL
   ,QTY                     varchar(255)    NOT NULL
   ,ODetail_QtyShipped      varchar(255)    NOT NULL
   ,WH                      varchar(255)    NOT NULL
   ,SalesRep                varchar(255)    NOT NULL
   ,Rep_Name                varchar(255)    NOT NULL
   ,EnteredBy               varchar(255)    NOT NULL
   ,WorkFlow                varchar(255)    NOT NULL
   ,PRIMARY KEY (OrderNumber)
) ENGINE = InnoDB;

And here's the error message:

Pre-execute (Error)

Messages

Error 0xc0047062: Data Flow Task 1: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ADONETDestination.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute (IDTSManagedComponentWrapper100 wrapper) (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: Destination - AllOrderData failed the pre-execute phase and returned error code 0x80004003. (SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 1: "Destination - AllOrderData" wrote 0 rows. (SQL Server Import and Export Wizard)

Please see screenshots below for each step:

  1. Step 1
  2. Step 2
  3. Step 3
  4. Step 4
  5. Step 5
  6. Step 6 and and Error
php
mysql
sql-server
asked on Stack Overflow Sep 11, 2018 by ScannedFrom Toshiba • edited Sep 11, 2018 by GYaN

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0