Sql一括コピーを使用して、最後のSQLテーブルに挿入した後のデータが見つかりません

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

質問

私はアクセステーブルからデータを抽出し、次にSQLの一括コピーを使用してtempテーブルからそれをメインSQLテーブルにロードするコンソールアプリケーションを作成しました。アプリケーションは期待どおりに動作しますが、アクセステーブルから26624行を抽出し、BulkコピーでWriteToServerを実行すると、24880行しかコピーされないため、メインSQLテーブルに挿入すると24880行しか挿入されません。約1744行が欠落していますが、例外が発生します

"SqlDateTimeオーバーフロー。1/1/1753 12:00:00 AMと12/31/9999 11:59:59 PMの間にある必要があります。

私はアクセスの列に開始時刻と停止時刻を設定していますが、これらの列にはデフォルト値のdata = 12/30/1899がありますが、時間は変わります。

データの移動に使用したコード:

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();
    }
}

助けてください!!!私はどのように1744の行が私はデフォルトを参照してくださいから12/30/1899を参照して挿入されていない理由を見つけることがわかりません1/1/1753 - 12/31/9999他の記事から私はそれを言う読むデフォルトの日付が1/1/0001に設定されている日付が添付されていない場合、予測された範囲外でオーバーフローが発生することがわかります。

受け入れられた回答

Accessでデータを調べ、それらの不良値を見つけて修正するか、エクスポート後にTSQLでそれらを処理する必要があります。デバッグする1つのオプション - tempテーブルを実際のテーブルに置き換え、datetimeデータ型をstringに置き換えて、エクスポートを行い、次にテーブルを照会して不正な値を見つけ、調べるデータの種類を知る。その後、一時表に戻すことができますが、インポート時には、おそらくCASEおよびCONVERT / CASTを使用して、SELECT文で無効な日付時刻書式を処理する必要があります。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ