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:
User contributions licensed under CC BY-SA 3.0