7
Simple list/export of all records with the TransferStatus = Error, Keyfields from index + ErrorText, so that can be possible to send this log as an excel list to the responsible person to correct the data.

Example in SQL:
/****** Script for generating of error messages with keys ******/
declare @ENTITYNAME nvarchar(50) = 'Data entity name';
declare @DEFINITIONGROUPNAME nvarchar(50) = 'Project name';
declare @IDXCOLUMNS nvarchar(max) ;
declare @TABLENAME nvarchar(50);

SET @TABLENAME = (SELECT TOP 1
LOGTABLE.STAGINGTABLENAME
FROM [AXDB].[dbo].[DMFSTAGINGVALIDATIONLOG] LOGTABLE

WHERE LOGTABLE.ENTITYNAME = @ENTITYNAME
AND [DEFINITIONGROUPNAME] = @DEFINITIONGROUPNAME)

declare @sql nvarchar(max);

print @TABLENAME;

SET @IDXCOLUMNS = (select-- SCHEMA_NAME (o.SCHEMA_ID) SchemaName

-- ,o.name ObjectName,i.name IndexNameS

-- ,i.type_desc

LEFT(list, ISNULL(splitter-1,len(list))) Columns

-- , SUBSTRING(list, indCol.splitter +1, 100) includedColumns--len(name) - splitter-1) columns

-- , COUNT(1) over (partition by o.object_id)

from sys.indexes i

join sys.objects o on i.object_id = o.object_id

cross apply (select NULLIF(charindex('|',indexCols.list),0) splitter , list

from (select cast((

select case when sc.is_included_column = 1 and sc.ColPos = 1 then '|' else '' end +

case when sc.ColPos > 1 then ', ' else '' end + name

from (select sc.is_included_column, index_column_id, name

, ROW_NUMBER() over (partition by sc.is_included_column

order by sc.index_column_id) ColPos

from sys.index_columns sc

join sys.columns c on sc.object_id = c.object_id

and sc.column_id = c.column_id

where sc.index_id = i.index_id

and sc.object_id = i.object_id ) sc

order by sc.is_included_column

,ColPos

for xml path (''), type) as varchar(max)) list)indexCols ) indCol


WHERE o.name = @TABLENAME AND i.name like '%STAGINGIDX')


set @sql = '


SELECT
LOGTABLE.ERRORMESSAGE, LOGTABLE.[STAGINGCOLUMNNAME], [DMFENTITY].ENTITYTABLE, ' + REPLACE(REPLACE(@IDXCOLUMNS, 'PARTITION',@TABLENAME+'.PARTITION AS STAGING_PARTITION'), 'EXECUTIONID',@TABLENAME+ '.EXECUTIONID AS STAGING_EXECUTIONID') + '
FROM [AXDB].[dbo].[DMFSTAGINGVALIDATIONLOG] LOGTABLE
LEFT JOIN [DMFENTITY] ON [DMFENTITY].ENTITYNAME = LOGTABLE.[ENTITYNAME]

AND [DEFINITIONGROUPNAME] = '''+@DEFINITIONGROUPNAME+'''
LEFT JOIN ' + @TABLENAME + ' on LOGTABLE.STAGINGRECID = ' +@TABLENAME + '.RECID
WHERE LOGTABLE.ENTITYNAME like '''+@ENTITYNAME+'''
AND LOGTABLE.[DEFINITIONGROUPNAME] = ''' +@DEFINITIONGROUPNAME + ''' '

print @sql
EXECUTE sp_executesql @sql

Category: Data Management
STATUS DETAILS
Needs Votes