1/17/2011

SSRS - Move reports to new folders through SQL

On my part time job, I was requested to find a way to move current reports with existing Subscriptions to new folder locations and maintain the existing subscriptions.

At the SQL Saturday this past weekend I was lucky enough to speak with Brian Knight, owner of Pragmatic Works. He was kind enough to clue me on a great trick.

In the ReportServer database, there is a table: [ReportServer].[dbo].[Catalog]

The two fields to be updated are PATH which contains the folder structure and PARENTID which contains the guid for the parent folder.

We would create the new folder structure manually and move the reports into those new folders based on SQL Update statements.


update [ReportServer].[dbo].[Catalog]
set ParentId='2B004818-C6C2-4348-9C45-87CF6A0FFCDB',
Path = '/Reports/Internal Reports/NewReportFolder/ExtraFastSQLCleanReport/'
where Name = 'ExtraFastSQLCleanReport'

And here's the SQL statement to view the update report in it's new location:

SELECT [ItemID]
,[Path]
,[Name]
,[ParentID]
,[Type]
,[Content]
,[Intermediate]
,[SnapshotDataID]
,[LinkSourceID]
,[Property]
,[Description]
,[Hidden]
,[CreatedByID]
,[CreationDate]
,[ModifiedByID]
,[ModifiedDate]
,[MimeType]
,[SnapshotLimit]
,[Parameter]
,[PolicyID]
,[PolicyRoot]
,[ExecutionFlag]
,[ExecutionTime]
FROM [ReportServer].[dbo].[Catalog]
WHERE Name = 'ExtraFastSQLCleanReport'



That's what I like most about programming with the Microsoft Stack.  The community is extensive and people are willing to share information.

Thanks!

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder