Earlier, we wrote a blog to download the multiple report files (.rdl files) at one go. You can find it here. Today, following readers’ requests, I will share a simple handy script which will help you to delete specific report files at once.
SSRS uses SQL Server to store it’s details and the Catalog and DataSource tables are used to store the report file details. The below script simply deletes the report from the Catalog and DataSource tables.
--Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer] USE [ReportServer] --Replace NULL with keywords of the ReportManager's Report Path, --if reports from any specific path are to be deleted DECLARE @FilterReportPath AS VARCHAR(500) = NULL --Replace NULL with the keyword matching the Report File Name, --if any specific reports are to be deleted DECLARE @FilterReportName AS VARCHAR(500) = NULL --Used to prepare the dynamic query DECLARE @TSQL AS NVARCHAR(MAX) --Prepare the query to delete the report files. SET @TSQL = STUFF((SELECT '; ' + ' ;DELETE ' + ' FROM ' + ' [dbo].[DataSource] ' + ' WHERE ' + ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''+ ' ;DELETE ' + ' FROM ' + ' [dbo].[Catalog] ' + ' WHERE ' + ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + '''' FROM [dbo].[Catalog] CL WHERE CL.[Type] = 2 --Report AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/') AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name) FOR XML PATH('')), 1,1,'') --SELECT @TSQL --Execute the Dynamic Query EXEC SP_EXECUTESQL @TSQL
© All Rights Reserved. Inkey Solutions 2020