Logo
banner

Blogs

SSRS – Delete specific RDL files from Report Server in one go

, January 11, 2018 405 Views

Introduction
Earlier, we wrote a blog to download the multiple report files (.rdl files) at one go. You can find it hereToday, following readers’ requests, I will share a simple handy script which will help you to delete specific report files at once.

Implementation
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

Happy Reporting!

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

Leave a Reply

Your email address will not be published. Required fields are marked *