sql-serverreporting-servicesssrs-2016ssrs-2019

Can I create folder on SQL Server Reporting Services using store procedure

i possible create new folder using gui method. Is it possible create new folder using mssql stored procedure? enter image description here

Solution

  • You can do this, the folders are just entries in the catalog table.

    The code below works but you might not need everything shown here.

    You can turn this script into an SP easily, it requires three parameters to be set.

    1. @FolderName - the name of the folder you want to create
    2. @UserName - The name of the user you want to show as the creating user
    3. @PolicyID - This relates (I think) to security, so I took this value from an existing folder that has the same security as the folder I wanted to create

    Here's the script

    DECLARE @FolderName varchar(75) = 'My Test Folder'
DECLARE @UserName varchar(75) = 'Joe.Bloggs'
DECLARE @PolicyID UNIQUEIDENTIFIER = '7e032071-6e30-46ab-9600-e0cc0a7adcf0' -- Taken from an existing folder with required permission

-- Get root folder
DECLARE @RootItemID UNIQUEIDENTIFIER = (SELECT ItemID FROM [Catalog] WHERE Type = 1 and Path = '')
-- Get creation User
DECLARE @UserID UNIQUEIDENTIFIER = (SELECT UserID FROM [Users] WHERE UserName = @UserName)

INSERT INTO [Catalog] ([ItemID], [Path], [Name], [ParentID], [Type], [Content], [Intermediate], [SnapshotDataID], [LinkSourceID], [Property], [Description], [Hidden], [CreatedByID], [CreationDate], [ModifiedByID], [ModifiedDate], [MimeType], [SnapshotLimit], [Parameter], [PolicyID], [PolicyRoot], [ExecutionFlag], [ExecutionTime], [SubType], [ComponentID])
SELECT 
  [ItemID] = NEWID()
, [Path] = '/' + @FolderName
, [Name] = @FolderName
, [ParentID] = @RootItemID
, [Type] = 1
, [Content] = NULL
, [Intermediate] = NULL
, [SnapshotDataID] = NULL
, [LinkSourceID] = NULL
, [Property] = '<Properties />'
, [Description] = NULL
, [Hidden] = 0
, [CreatedByID] = @UserID
, [CreationDate] = getdate()
, [ModifiedByID] = @UserID
, [ModifiedDate] = getdate()
, [MimeType] = NULL
, [SnapshotLimit] = NULL
, [Parameter] = NULL
, [PolicyID] = @PolicyID
, [PolicyRoot] = 1
, [ExecutionFlag] = 1
, [ExecutionTime] = NULL
, [SubType] = NULL
, [ComponentID] = NULL