SQLBulkCopy 和日期 (1/1/1753)
我有一个应用程序，它已经工作了很长一段时间，但有一个恼人的项目有时仍然会妨碍我。
假设我使用 OracleDataReader 或 MySQLDataReader 等对象将数据传递给 sqlbulkcopy 对象进行插入。让我们假设所有列都映射得很好，并且在大多数情况下，一切都运行良好。
当然，我无法控制源应用程序或数据库（MySQL 或 Oracle）。因此，一些错误进入了不同的应用程序，并在发票表上输入了 5/31/0210 的日期。他确实打算输入 2010 年 5 月 31 日，但他使用的应用程序没有非常严格地验证数据，而 Oracle 数据库接受它。对于所有密集目的，210 年 5 月 31 日的数据是 Oracle 数据库的有效日期。就数据输入而言，这可能很愚蠢，但目前就是这样。
现在我们的 OracleDataReader 出现了，并通过 SQLBulkCopy 将此发票表传输到 SQL Server。它将数据传递到具有正确列名和数据类型的完全匹配的表。你可以看到将会发生什么。 SQL Server 数据库引擎不接受来自 Oracle 的 05/31/0210 日期，因为 DATETIME 字段仅允许从 1/1/1753 到 12/31/9999 的日期。
当它遇到这条记录时，它就会失败并给出溢出错误。它不会跳过记录，而是杀死提要。因此，如果一百万条记录表中有一千条记录，您将无法获得剩余的 999,000 条记录。
有没有办法解决这个问题以便继续提供？
理想情况下，我想将接收 SQL Server DB 移至 2008 并使用 DATETIME2，这将允许这些愚蠢的日期，但不幸的是，并非所有客户都准备好移至此版本，因此我坚持使用 DATETIME SQL 2000/2005/2008。
关于如何在不更改 SQL 的情况下解决这个问题的任何想法吗？理想情况下，我不介意它只是跳过记录。我知道我可以在 datareder 的 SQL 中执行此操作，但是当单个查询中有 20 个日期字段时，这将非常复杂。这将是维护的噩梦。
任何想法将不胜感激。
I've got an application which has been working fine for quite a while, but there is an annoying item that continues to get in the way on occasion.
Let's say that I use an object such as OracleDataReader or MySQLDataReader to pass the data to the sqlbulkcopy object for insert. Let's assume that all the columns maps just fine and for the most part, it all works well.
Granted, I don't have control over the source application or database (which is either MySQL or Oracle). So some goof goes into a different application and puts in a date on the invoice table of 5/31/0210. He really meant to put in 5/31/2010, but the application he's using is not validating the data very tightly and the Oracle database accepts it. For all intensive purposes, the data of 5/31/0210 is a valid date for the Oracle db. It might be stupid in terms of data entry, but it is what it is at this point.
Now our OracleDataReader comes along and is transferring this invoice table over to SQL Server via the SQLBulkCopy. It is passing the data to perfectly matched table with the right column names and data types. You can see what is going to happen. This date of 05/31/0210 from Oracle is not accepted by the SQL Server db engine, as the DATETIME field only allows dates from 1/1/1753 to 12/31/9999.
When it encounters this record, it simply fails and gives an overflow error. It doesn't skip the record, it kills the feed. So if it happens a thousand records in on a million record table, you don't get the remaining 999,000 records.
Is there anyway to get around this issue so that the feed will continue?
Ideally, I'd like to move the receiving SQL Server DB to 2008 and use DATETIME2, which would allow for these goofy dates, but unfortunately not all my clients are ready to move to this version yet, so I'm stuck with DATETIME in SQL 2000/2005/2008.
Any ideas on how to get around this without changing the SQL? Ideally, I wouldn't mind if it just skipped the record. I know that I could do this in the SQL for the datareder, but this would be extremely complicated when you have twenty date fields in a single query. It would be maintenance nightmare.
Any thoughts would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一种选择是将日期时间列类型更改为 varchar。然后添加一个派生列，用于将字符串转换为日期时间。技巧是使用派生列中的函数来验证日期，并在转换失败时输入任意日期时间。如果您进行大量日期比较，请保留计算列并/或为其建立索引。
我说这一切的印象是 sqlbulkcopy 无法进行转换。也许你可以。希望有人能提供一种方法。
在这种情况下，SSIS 会非常有用，因为您可以进行转换并获得批量更新锁的性能优势。
One option would be to change the datetime column type to varchar. Then add a derived column for converting the string to datetime. The trick would be to use a function in the derived column to validate the date and put an arbitrary datetime if the coversion will fail. If you do heavy date comparisons, persist the computed column and/or index it.
I say all of this under the impression that sqlbulkcopy is not able to do transforms. Maybe you can. Hopefully, someone will chime in with a way to.
SSIS would be great in this situation, as you could do the transform and also get the performance benefits of the bulk update lock.