I wrote an console application that extracts data from my access table then using sql bulk copy to an temp sql table then from the temp table loads it into main sql table. the application works as expected but it extracts 26624 rows from access table and when it does a WriteToServer on Bulk copy it only copies 24880 rows and hence when inserted into the main sql table only 24880 rows get inserted. about 1744 rows are missing but i do get an exception
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."
i have columns in access that is start time and stop time these columns have an default value of data = 12/30/1899 but the time changes.
The code i used to move data:
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.KeepIdentity, tran))
{
sbc.BatchSize = 10;
sbc.NotifyAfter = 100;
sbc.BulkCopyTimeout = 1000;
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();
}
}
please help!!! and i am not sure how to find out why that 1744 rows are not getting inserted from what i see the default 12/30/1899 is between 1/1/1753 - 12/31/9999 from other articles i read it says that if there was not date attached it sets the default date to 1/1/0001 then i can understand it is out of the expected range hence causing the overflow.
you need to look at the data in Access, and find/fix those bad column values, or handle them in your TSQL after the export. One option to debug - replace temp table with real table, replace datetime datatype with string, do your export, then query the table to find the bad values so you know what kind of data to look for. After that, you can switch it back to temp table, but in your import, you should handle the invalid date time formats in the SELECT statement, probably using CASE, and CONVERT/CAST.