CREATE PROCEDURE dbo.usp_DiabloFailedRowDetail
@FullPath VARCHAR(4000),
@RowNumber INT,
@FileSize BIGINT = 0 OUTPUT,
@CreateDate VARCHAR(30) = '' OUTPUT,
@LastWrittenTo VARCHAR(30) = '' OUTPUT,
@Folder VARCHAR(1000) = '' OUTPUT,
@FileName VARCHAR(1000) = '' OUTPUT,
@Output VARCHAR(MAX) = '' OUTPUT,
@Delimiter VARCHAR(30) = '~',
@PrintOutput BIT = 0
AS
SET NOCOUNT ON
DECLARE @Line VARCHAR(MAX),
@Message VARCHAR(400),
@Result INT
SET @Output = ISNULL(@Output, '')
SELECT @FileName = FileName,
@Folder = Directory,
@FileSize = SIZE,
@CreateDate = CONVERT(VARCHAR(10), CreateDate, 101) + ' ' + CONVERT(VARCHAR(8), CreateDate, 114),
@LastWrittenTo = CONVERT(VARCHAR(10), LastWritten, 101) + ' ' + CONVERT(VARCHAR(8), LastWritten, 114)
FROM FS.GetFileInfo(@FullPath)
IF @@ROWCOUNT = 0
BEGIN
SET @Output = @Output + 'File "' + @FullPath + '" Not Found'
RETURN 10
END
-- Result 1 char(0) Terminated
IF @FileSize > 0
SELECT @Line = Line,
@Message = MESSAGE,
@Result = RESULT
FROM FS.ReadLine(@FullPath, @RowNumber, 100000) ;
WITH RowDetail
AS (SELECT TOP 999999
a.Field,
a.FieldNum
FROM dbo.ParseDelimited(ISNULL(@Line, ''), @Delimiter) a
ORDER BY a.FieldNum)
SELECT @Output = @Output
+ (SELECT 'TableName: ' + ISNULL(b.TableName, '') + '
FileSize: ' + CAST(@FileSize AS VARCHAR) + ' (' + CAST(CAST(@FileSize * 1.0 / 1024 / 1024 AS NUMERIC(20, 2)) AS VARCHAR)
+ 'MB)
CreateDate: ' + @CreateDate + '
LastWrittenTo: ' + @LastWrittenTo + '
Path: ' + @FullPath + '
RowNumber: ' + CAST(@RowNumber AS VARCHAR) + '
RowLength: ' + ISNULL(CAST(LEN(@Line) AS VARCHAR), '') + '
Row: ' + ISNULL(@Line, 'NULL') + '
' + ISNULL('Message: ' + @Message + '
', '') + CASE WHEN @Result = 1 THEN 'Error: !!! NULL CHARACTER FOUND !!!
' ELSE ''
END
FROM (SELECT dbo.StringConcat(TableName, ', ') AS TableName
FROM (SELECT TableName FROM dbo.DiabloSpecTableMapping (NOLOCK)
WHERE FlatFileName = @FileName) k) b) + '
' + CASE WHEN @Result = 1 THEN ''
ELSE (SELECT dbo.StringConcat(CAST(ISNULL(a.FieldNum, b.FieldPosition) AS VARCHAR) + ', '
+ ISNULL(b.MainframeFieldName, 'NULL') + '(' + ISNULL(b.MfFormat, 'NULL')
+ ISNULL(' [DivideBy: ' + CAST(DivideBy AS VARCHAR) + ']', '') + ') => '
+ ISNULL(b.ColumnName, 'NULL') + '(' + ISNULL(LoadFormat, 'NULL') + ') : '
+ ISNULL(a.Field, 'NULL'), '
')
FROM (SELECT * FROM dbo.DiabloSpecColumnMapping B (NOLOCK) WHERE B.FlatFileName = @FileName) B
FULL OUTER JOIN RowDetail a ON a.FieldNum = B.FieldPosition)
END
IF @PrintOutput = 1
SELECT CAST(Field AS VARCHAR(MAX))
FROM dbo.ParseDelimited(@Output, '
')
RETURN 0