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
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
STATUS DETAILS
Needs Votes