Inkey Solution Logo


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

, January 11, 2018 8351 Views

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.

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
   --Prepare the query to delete the report files.
                      '; ' +
                      ' ;DELETE ' +
                      ' FROM ' +
                      ' [dbo].[DataSource] ' +
                      ' WHERE ' +
                      ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''+
                      ' ;DELETE ' +
                      ' FROM ' +
                      ' [dbo].[Catalog] ' +
                      ' WHERE ' +
                      ' ItemID = ''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''
                      [dbo].[Catalog] CL
                      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,'')
  --Execute the Dynamic Query

Happy Reporting!



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 *

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?