You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In this case there are multiple tables in the report datasets. So if one of the tables are missing, but the main one is there - dbo.tbl_dm_db_file_space_usage, the report will be shown as clickable in the SQL Perf Main
insert into @tbl_reports (ReportName, ReportDisplayName, ReportDescription, VersionApplied, Category, ValidationObject, SeqNo, DataCollection,Manditory, CategorySeq)
values ('Tempdb_Space_Use_C', 'Tempdb Space Usage', 'Breakdown of space usage in tempdb', 1|2|4|8, 'Performance', 'dbo.tbl_dm_db_file_space_usage', 4000, 'Tempdb and Trans Collector', 1,3000)
And yet when you click the report, you can get a failure because another table is missing
The text was updated successfully, but these errors were encountered:
Possible solution: re-architect DataSet_Reports queries to somehow account for multiple tables, not just one.
One quirky solution, that may work is:
select
CategorySeq, Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, SeqNo, case when (Sum(ReportAvailable) -Count(ReportAvailable)) =0 then 1 else 0 end ReportAvailable
from
(
select CategorySeq,Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, case when indx.rowcnt>0 then 1 else 0 end ReportAvailable, SeqNo
from @tbl_reports rep
left outer join sysindexes indx on ( OBJECT_ID (rep.ValidationObject) =indx.idandindx.indidin (0,1,300))
) t
group by CategorySeq, Category, ReportName, ReportDisplayName, ReportDescription, DataCollection, SeqNo
order by CategorySeq, SeqNo, ReportName
PiJoCoder
changed the title
If a report uses multiple tables it may be shown on SQL Perf Main report but not all tables are present
If a report uses multiple tables it should not appear on SQL Perf Main report because not all tables are present
Aug 14, 2023
PiJoCoder
changed the title
If a report uses multiple tables it should not appear on SQL Perf Main report because not all tables are present
If a report uses multiple tables it should not appear on SQL Perf Main report when not all tables are present
Aug 14, 2023
This modification can be tested via just renaming some tables and seeing corresponding reports disappearing from SQL Perf Main. This should prevent errors similar to the screenshot on this issue.
In this case there are multiple tables in the report datasets. So if one of the tables are missing, but the main one is there - dbo.tbl_dm_db_file_space_usage, the report will be shown as clickable in the SQL Perf Main
insert into @tbl_reports (ReportName, ReportDisplayName, ReportDescription, VersionApplied, Category, ValidationObject, SeqNo, DataCollection,Manditory, CategorySeq)
values ('Tempdb_Space_Use_C', 'Tempdb Space Usage', 'Breakdown of space usage in tempdb', 1|2|4|8, 'Performance', 'dbo.tbl_dm_db_file_space_usage', 4000, 'Tempdb and Trans Collector', 1,3000)
And yet when you click the report, you can get a failure because another table is missing
The text was updated successfully, but these errors were encountered: