时间戳

首页 > 解决方案 > 在输出消息中添加有关 PK 值的信息 - 更容易找到有缺陷的记录

sql-server - 在输出消息中添加有关 PK 值的信息 - 更容易找到有缺陷的记录

问题描述

我是一名助理数据经理，我们每天处理数千条数据记录。我一直盲目地试图找到一个字段，例如日期转换错误，我搜索和搜索。有时它很明显且易于修复，但通常并非如此。

我们有一个输入原始数据的“原始”表。然后，我们使用存储的代码过程来处理和查询数据，同时将其移动到“验证”或“存储”中。如果有错误，则将数据移至“验证”，如果没有错误，则将这些数据记录移至“已存储”。

当数据被移动并且使我们编码的逻辑语句失败的数据时，每次失败都会生成错误日志。我尝试以 10 个为一组进行此操作，以保持较低的记录数量，但在每条记录中最多可以检查 100 个字段。即 1,000 个单独的字段来检查日期时间错误或真正的十进制错误，这是最常见的错误类型。

消息 241，级别 16，状态 1，第 3 行
从字符串转换日期和/或时间时转换失败。

如果上面是运行时错误与我的书面编码逻辑“if else”错误的示例，那么我如何让它传递主键值，以便我知道它是哪一行，例如 10 行数百行要检查的字段数是有错误的行。

我在错误检查和更正数据的过程中一直失明.. 我该如何改进上面的错误信息？我希望上面的错误消息能够输出并说 PK = 000100 .. 所以：

消息 241，级别 16，状态 1，第 3 行
从字符串转换日期和/或时间时转换失败。
PK = 000100

有没有办法做到这一点？

我正在使用 Microsoft SQL Server Management Studio v17.2 程序，我们正在运行 SQL Server 2016。

谢谢。

标签： sql-servererror-handlingruntime-error

解决方案

好吧，你可以这样做，但你必须问正确的问题。

问题：SQL server 吐出错误时可以截取PK行吗？

答：嗯，是的，但仅适用于一个追加查询并且如果它是一行。

真正的答案：好吧，然后对每一行使用附加查询，这样您就可以获取并获取 PK，这将提供您所需的信息。

假设我有这个附加：

INSERT INTO TimeTest
     (CustomerNames, StartTime, EndTime, StartTimeT, EndTimeT, StartTimeD)
SELECT
     CustomerNames, StartTime, EndTime, StartTimeT, EndTimeT, StartTimeD
FROM  MySource

在上面，目标表 (TimeTest) 具有强类型的日期 + 时间列。

源表 (MySource) 将每一列都作为文本类型（某种字符）列。该数据是导入的结果。因此，可以并且将会有数据列是错误的和不正确的日期列。

所以，当运行上面的，我得到这个：

Msg 242, Level 16, State 3, Line 2
The conversion of a nvarchar data type to a datetime data type 
resulted in an out-of-range value.
The statement has been terminated.

现在，当然，我想要上面的PK。所以，我建议使用游标。使用光标，我可以在追加一行之前获取 PK。

事实上，我也会在十进制列表和日期列中编码，并且当发生转换错误时，代码也会吐出实际的列名。

我还建议您构建某种前端（例如在 Access 或 vb.net 等中），然后可以显示 + 处理这些不良记录。该用户系统还应该能够记住修复和编辑。并且编辑+保存会将这个固定的行直接发送到服务器表（最后的休息表）中。

无论如何，这是一个可以执行此操作的存储过程：

Create PROCEDURE MyInsertTrap
AS
BEGIN

DECLARE @PKID int
DECLARE @MyCol as nvarchar(50)
DECLARE @MyErr as nvarchar(200)

set nocount on

DECLARE db_cursor CURSOR FOR
SELECT        id 
FROM          MySource

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @pkid 
WHILE @@FETCH_STATUS = 0
    BEGIN   

       BEGIN TRY
       INSERT INTO TimeTest
              (CustomerNames, StartTime, EndTime, StartTimeT, EndTimeT, StartTimeD)
        SELECT
              CustomerNames, StartTime, EndTime, StartTimeT, EndTimeT, StartTimeD
        FROM MySource where MySource.id = @pkid;

       END TRY
       BEGIN CATCH
          -- Error in append, show pk, + error
          PRINT 'ERROR IN APPEND FOR PK = ' + cast( @pkid as nvarchar(10))
          PRINT ERROR_MESSAGE()

       END CATCH

       FETCH NEXT FROM db_cursor INTO @pkid 
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
END

上面的输出：

 ERROR IN APPEND FOR PK = 14522
 The conversion of a nvarchar data type to a datetime data type 
 resulted in an out-of-range value.

请注意，我们可以将此不良记录附加到其他一些修复表中。我们甚至可以进行额外的处理。

因此，如果我们使用逐行“光标”处理，我们不仅会捕获无法追加的一行，而且我们甚至可以开始添加代码，将实际失败的列吐出，甚至将记录发送到一些桌面程序，允许人们编辑+查看每条记录，更正并点击一个按钮，然后将记录发送到决赛桌。

使用此代码，追加查询不会在错误的行上停止，而是继续查询所有记录。输出将只显示失败的行，但有效的行将通过此错误捕获。

推荐阅读