Date Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM OverFlow error SqlBulkCopy

c# console-application datetime sqlbulkcopy sql-server-2008

Question

I'm using the code below to take data from an access database, save it in a temporary sql table, truncate the main sql table, and then insert the new data set. However, the datetime is causing me problems:

Console.WriteLine("NetWeightTracking-Abilene Started");
            var du = new System.Data.DataTable();
            string accdbConnStrabk = ConfigurationManager.ConnectionStrings["NetWeightAbk"].ToString();
            using (var accdbConn = new OdbcConnection(accdbConnStrabk))
            {
                using (var da = new OdbcDataAdapter("SELECT * FROM [Net Weight Tracking]", accdbConn))
                {
                    da.Fill(du);

                }
            }

            Console.WriteLine("DataTable filled from NetWeight db NetWeightTracking-Abilene - Row count: {0}", du.Rows.Count, DateTime.Now.ToString());

            string sqlConnStrabk = ConfigurationManager.ConnectionStrings["sqlconabk"].ToString();
            using (var sqlConn = new SqlConnection(sqlConnStrabk))
            {
                sqlConn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = sqlConn;
                    cmd.CommandText = "CREATE TABLE #NetWeightTracking ([Date] [datetime] NULL,[Unit UPC Base Item] [nvarchar](50) NULL,[Item (Optional)] [nvarchar](50) NULL,[Preset Number] [nvarchar](50) NULL,[Product Group] [nvarchar](255) NULL,[Shift] [nvarchar](255) NULL,[Rotation Code] [nvarchar](255) NULL,[BBD] [nvarchar](255) NULL,[Operator Name] [nvarchar](255) NULL,[Supervisor] [nvarchar](255) NULL,[Production Line] [nvarchar](255) NULL,[Bagger Number] [float] NULL,[Start Time] [datetime] NULL,[Stop Time] [datetime] NULL,[Under Counts] [float] NULL,[Label Wt on Pkg (g)] [float] NULL,[Machine Tare Wt (g)] [float] NULL,[Actual Tare Wt (g)] [float] NULL,[Verify Target Wt (g)] [float] NULL,[Total Count (Proper)] [float] NULL,[Mean Gross (g)] [float] NULL,[Rptd Mean Net (g)] [float] NULL,[Std Dev (g)] [float] NULL,[Max (g)] [float] NULL,[Min (g)] [float] NULL,[TNE (g)] [float] NULL,[Comments] [nvarchar](50) NULL,[Field1] [datetime] NULL,[Field2] [datetime] NULL,[Field3] [nvarchar](255) NULL,[Field4] [nvarchar](255) NULL,[Field5] [nvarchar](255) NULL,[Field6] [nvarchar](255) NULL,[Field7] [nvarchar](255) NULL, [Row] [int] IDENTITY(1,1) NOT NULL)";
                    cmd.ExecuteNonQuery();
                }

                using (SqlTransaction tran = sqlConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        using (var sbc = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, tran))
                        {

                            sbc.BatchSize = 100;
                            sbc.NotifyAfter = 100;
                            sbc.BulkCopyTimeout = 100;
                            sbc.DestinationTableName = "#NetWeightTracking";
                            Console.WriteLine(DateTime.Now.ToString());
                            sbc.WriteToServer(du);
                            Console.WriteLine("After Datatable", DateTime.Now.ToString());
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message.ToString());
                    }

                    Console.WriteLine(DateTime.Now.ToString());
                    using (var cmd = new SqlCommand())
                    {
                        cmd.Connection = sqlConn;
                        cmd.Transaction = tran;
                        cmd.CommandText = "SELECT COUNT(*) AS n FROM #NetWeightTracking";
                        Console.WriteLine("SqlBulkCopy complete. Temp table row count: {0}", cmd.ExecuteScalar());
                        cmd.CommandText = "TRUNCATE TABLE [dbo].[Net Weight Tracking]";
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("Truncated NetWeightTrackingTable");

                        cmd.CommandText = "INSERT INTO [dbo].[Net Weight Tracking] ([Date],[Unit UPC Base Item],[Item (Optional)],[Preset Number],[Product Group],[Shift],[Rotation Code],[BBD],[Operator Name],[Supervisor],[Production Line],[Bagger Number],[Start Time],[Stop Time],[Under Counts],[Label Wt on Pkg (g)],[Machine Tare Wt (g)],[Actual Tare Wt (g)],[Verify Target Wt (g)],[Total Count (Proper)],[Mean Gross (g)],[Rptd Mean Net (g)],[Std Dev (g)],[Max (g)],[Min (g)],[TNE (g)],[Comments],[Field1],[Field2],[Field3])  SELECT Z.[Date],Z.[Unit UPC Base Item],Z.[Item (Optional)],Z.[Preset Number],Z.[Product Group],Z.[Shift],Z.[Rotation Code],Z.[BBD],Z.[Operator Name],Z.[Supervisor],Z.[Production Line],Z.[Bagger Number],Z.[Start Time],Z.[Stop Time],Z.[Under Counts],Z.[Label Wt on Pkg (g)],Z.[Machine Tare Wt (g)],Z.[Actual Tare Wt (g)],Z.[Verify Target Wt (g)],Z.[Total Count (Proper)],Z.[Mean Gross (g)],Z.[Rptd Mean Net (g)],Z.[Std Dev (g)],Z.[Max (g)],Z.[Min (g)],Z.[TNE (g)],Z.[Comments],Z.[Field1],Z.[Field2],Z.[Field3] FROM  #NetWeightTracking Z";
                        Console.WriteLine(DateTime.Now.ToString());
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "SELECT COUNT(*) AS m FROM [dbo].[Net Weight Tracking]";
                        Console.WriteLine("Inserted Records into NetWeightTracking:{0}", cmd.ExecuteScalar());

                    }
                    tran.Commit();
                }
            }
        }

Any assistance would be highly appreciated.

I tried looking for any null dates in the Access database, but all the dates are accessible. I came saw the following solution online:

DateTime.ParseExact (txtPunchDate.Text, "yyyy-MM-dd" , null)

What would it look like in my console application? Thanks:)

1
0
6/4/2015 10:58:32 PM

Accepted Answer

Alright, Microsoft Access is itsdatetime kind of data as adouble:

  • The MS calendar's epoch (zero point) is30 December 1899 00:00:00
  • The decimal part of thedouble is the offset from the epoch in days, and
  • The little amount of thedouble is the brief period of the day.

The standard states that the date field in an MS Accessdatetime is

  • lower limit:1 January 100
  • upper limit:31 December 9999

Additionally, a SQL Server's domaindatetime is:

  • lower limit:1 January 1753
  • upper limit:31 December 9999

There will be issues with any dates in your MS Access database that are earlier than January 1, 1753. You must locate the false data and correct it. Several strategies:

  • Create a view or query in your Access database to display the data in a way that SQL Server can understand. After that, bulk-load data into SQL Server.

  • When bulk loading data into SQL Server, it's common practice to bulk load the source data into a functioning table with all columns set to nullable, of type "nullable," as it's almost a given that the source data is unclean or malformed.varchar kinds without any restrictions or keys. Run a stored procedure to clean up and manipulate the data as needed before relocating it to its correct location when that has been completed.

1
6/4/2015 11:58:05 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow