Almost inevitably the day after you’ve cleaned up your reports someone will suddenly remember a super urgent report that hasn’t been run for a dogs age but they need now! The following script will move reports which were archived back to the original location creating the folder hierarchy as it goes if you pass in the ItemID for the report needed.
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @itemID AS UNIQUEIDENTIFIER = ''
DECLARE @User AS UNIQUEIDENTIFIER
DECLARE @Policy AS UNIQUEIDENTIFIER
DECLARE @ParentID AS UNIQUEIDENTIFIER
DECLARE @Path NVARCHAR(500),@Name NVARCHAR(500)
WHILE EXISTS(
SELECT REPLACE(REPLACE(clg.Description,'Moved From: ',''),'/'+clg.Name,'') , '/'+a.val--, a.r
FROM dbo.Catalog AS clg
OUTER APPLY (SELECT id,val
FROM ConvertToFolderHierarchy(REPLACE(REPLACE(clg.Description,'Moved From: ',''),'/'+clg.Name,''),'/')
) AS a
WHERE ItemID = @itemID
AND NOT EXISTS(SELECT 1 FROM dbo.Catalog AS sc WHERE sc.path = '/'+a.val)
)
BEGIN
SELECT TOP 1 @Path = a.val, @Name = a.NameOnly, @ParentID = pid, @Policy = Parent.PolicyID
--REPLACE(REPLACE(clg.Description,'Moved From: ',''),'/'+clg.Name,'') , a.val, Parent.[PID], a.nameonly
FROM dbo.Catalog AS clg
OUTER APPLY (SELECT
ISNULL(id,'') AS [id]
,CASE WHEN val IS NULL THEN '' ELSE '/'+val END AS [val],
ISNULL(NameOnly,'') AS [NameOnly]
FROM ConvertToFolderHierarchy(REPLACE(REPLACE(clg.Description,'Moved From: ',''),'/'+clg.Name,''),'/')
) AS a
OUTER APPLY (
SELECT
ItemID AS [PID] ,
path AS [pp],
name AS [pn],
PolicyID
FROM dbo.Catalog
WHERE path = LEFT(ISNULL(a.val,''),LEN(ISNULL(a.val,''))-(CHARINDEX('/',REVERSE(ISNULL(a.val,'')))))
) AS [Parent]
WHERE ItemID = @itemID
AND NOT EXISTS(SELECT 1 FROM dbo.Catalog AS sc WHERE sc.path =a.val)
ORDER BY id ASC
SELECT @User = UserID
FROM dbo.Users
WHERE UserName = ORIGINAL_LOGIN()
INSERT INTO dbo.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
)
VALUES ( NEWID() , -- ItemID - uniqueidentifier
@Path, -- Path - nvarchar(425)
@Name , -- Name - nvarchar(425)
@ParentID , -- ParentID - uniqueidentifier
1 , -- Type - int
NULL , -- Content - image
NULL , -- Intermediate - uniqueidentifier
NULL , -- SnapshotDataID - uniqueidentifier
NULL , -- LinkSourceID - uniqueidentifier
'<Properties />' , -- Property - ntext
NULL , -- Description - nvarchar(512)
NULL , -- Hidden - bit
@User , -- CreatedByID - uniqueidentifier
GETUTCDATE() , -- CreationDate - datetime
@User, -- ModifiedByID - uniqueidentifier
GETUTCDATE() , -- ModifiedDate - datetime
NULL , -- MimeType - nvarchar(260)
0 , -- SnapshotLimit - int
NULL , -- Parameter - ntext
@Policy , -- PolicyID - uniqueidentifier
0 , -- PolicyRoot - bit
1 , -- ExecutionFlag - int
NULL , -- ExecutionTime - datetime
NULL , -- SubType - nvarchar(128)
NULL -- ComponentID - uniqueidentifier
)
END
UPDATE clg
SET ParentId = pathclg.ItemID ,
Path = REPLACE(clg.Description,'Moved From: ','')
FROM dbo.Catalog AS clg
INNER JOIN dbo.Catalog AS pathclg
ON REPLACE(REPLACE(clg.Description,'Moved From: ',''),'/'+clg.Name,'') = pathclg.path
WHERE clg.ItemID = @ItemID
ROLLBACK
This code uses the following function to return the hierarchy of folders to create.
CREATE FUNCTION [dbo].[ConvertToFolderHierarchy](@stringArray VARCHAR(MAX), @delimiter CHAR(1))
RETURNS @StringArrayTable TABLE (ID INT IDENTITY(1,1) ,val VARCHAR(500),NameOnly VARCHAR(500))
AS
BEGIN
DECLARE @delimiterPosition INT =0
IF(RIGHT(@stringArray,1) != @delimiter)
BEGIN
SET @stringArray = @stringArray + @delimiter
END
IF(LEFT(@stringarray,1) = @delimiter)
BEGIN
SET @stringArray = RIGHT(@stringarray,LEN(@stringarray)-LEN(@delimiter))
END
DECLARE @StringArray2 AS VARCHAR(500) = @stringArray
DECLARE @delimiterPosition2 INT =0
--SELECT @stringArray, CHARINDEX(@delimiter, @stringArray, @delimiterPosition),@delimiterPosition
WHILE(CHARINDEX(@delimiter, @stringArray, @delimiterPosition+1) <> 0)
BEGIN
SELECT @delimiterPosition = CHARINDEX(@delimiter, @stringArray,@delimiterPosition+1)
SELECT @delimiterPosition2 = CHARINDEX(@delimiter, @stringArray2)
INSERT INTO @stringarraytable(val,NameOnly)
SELECT LEFT(@stringArray, @delimiterPosition - 1),LEFT(@stringArray2, @delimiterPosition2 - 1)
SELECT @stringArray2 = STUFF(@stringArray2, 1, @delimiterPosition2, '')
END
RETURN
END
Secret SQL 