question

MichaelEdwards-6279 avatar image
0 Votes"
MichaelEdwards-6279 asked LukasYu-msft commented

SSRS: bulk rename reports

Hi.

We have many legacy reports that have not been executed in a long time. I can find these from a persisted version of the ExecutionLogStorage table (or the fact that they are not in there at all but are in the catalog table).

I have tested then renaming these with a TOBEDELETED_YYYYMMDD suffix, as simply as this:

UPDATE
ReportServer.dbo.catalog
SET name = name + '_TOBEDELETED_20210309' , path = Path +'_TOBEDELETED_20210309'
--- where ......

This appears to work completely fine. The reports appear in the SSRS web app with the new names and still run fine. The idea is that in 6 months time I will use a PowerShell script to permanently delete these reports.

Can anyone see any potential problems with this approach? Thanks!

PS. Have more recently spotted (using SQL Profiler when renaming a report from the front end) this proc: ReportServer..MoveObject:

EXECUTE @RC = [dbo].[MoveObject]
@OldPath
,@OldPrefix
,@NewName
,@NewPath
,@NewParentID
,@RenameOnly
,@MaxPathLength

Any thoughts on using that instead?

Lastly PowerSell does not seem to support the MoveItem method, but DotNet does (https://docs.microsoft.com/en-us/dotnet/api/reportservice2010.reportingservice2010.moveitem?redirectedfrom=MSDN&view=sqlserver-2016#ReportService2010_ReportingService2010_MoveItem_System_String_System_String_)

Is anyone aware if I am wrong about this and rename is possible through PowerShell? Thanks

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

Hi,

Can anyone see any potential problems with this approach? Thanks!

From the server side , I don't think there are much problem with this operation. You could get some problems with linked-report or subreport, since the name changed. Other function should not affected to much as they are using ID to be joined. But still, generally it is not suggested to directly operate on the database level data, I could also fully thought through what is the potential risk. But an operation like this, you could always recall it.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you implement your solution with powershell? Have you made any progress on this ?

0 Votes 0 ·

I wrote a C# console app in the end. Seems PowerShell does not support the MoveItem method strangely.

If you thought it would be useful I could copy and paste the majority of my code.

Thanks for your help.


Mike

0 Votes 0 ·

Yes ! We would love to see and learn from your code . Apprieciating it in advance ! :)

0 Votes 0 ·