/****** 
SCRIPT: UpdatePaths
Created: May 25 2010
Created By: Mark Terry

PURPOSE: The purpose of this procedure is to replace the Path in the catalog table for reports which are about to be distributed by email. 
It does this so that the filename of the attached report will have the date in the file name. 
In order for this to work it must be executed by a SQL Job every 15 minutes. 
During the 15 minutes that the path has been changed, you cannot access from report server directly as the path has been changed.

REQUIREMENTS: You must setup a SQL Job on the same server as the ReportServer database to run every 15 minutes (unless timing changed) for this to work correctly. 

Tweaks: You can change the line 
Set Path = Path + ' ' +  CONVERT(Char(15), getdate(), 106) to Set Path = Path + ' ' +  Whatever you want
You can change the timing by changing the between 0 and 15 ranges at the end of the procedure.

Latest Updates: Changed the dates it is looking for as it didn't work over midnight runs.

  ******/
ALTER Procedure [dbo].[UpdatePaths] as
Begin
--This procedure updates paths for report subscriptions
--the report subscription typically needs to be on a shared subscription for this to work. Maybe changing the dates I am looking at will make it work
IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Catalog_Path') 
	CREATE TABLE [dbo].[Catalog_Path](
		[ItemID] [uniqueidentifier] NOT NULL,
		[PriorPath] [nvarchar](425) NULL
	) ON [PRIMARY]   

Insert Into Catalog_Path (ItemID, PriorPath)
Select ItemID, Path from Catalog Where Catalog.ItemID not in (Select ItemID from Catalog_Path)

Delete from Catalog_Path
Where ItemID not in (Select ItemID from Catalog)

UPDATE Catalog
Set Path = PriorPath
FROM  
(SELECT
	cp.ItemID as PastItemID,
	cp.PriorPath
FROM Catalog_Path cp
)Past
Where Catalog.ItemID = PastItemID
And Path like '%' +  replace(Cast(YEAR(GetDate()) as varchar), '201', '1')
And Catalog.ItemID in
(Select ItemID FROM        
	ReportSchedule rs            
INNER JOIN subscriptions s                
	ON rs.subscriptionID = s.subscriptionID            
INNER JOIN dbo.[catalog] c                
	ON rs.reportID = c.itemID    
Inner Join Schedule sc
	ON sc.ScheduleID = rs.ScheduleID
WHERE        
sc.LastRunTime < GETDATE() and not
(DATEDIFF(MINUTE,GETDATE(),NextRunTime) between 0 and 20
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 20))


UPDATE Catalog
Set Path = Path + ' ' +  Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','')
FROM  
(SELECT
	c.ItemID as FutureItemID
FROM        
	ReportSchedule rs            
INNER JOIN subscriptions s                
	ON rs.subscriptionID = s.subscriptionID            
INNER JOIN dbo.[catalog] c                
	ON rs.reportID = c.itemID    
Inner Join Schedule sc
	ON sc.ScheduleID = rs.ScheduleID
WHERE        
DATEDIFF(MINUTE,GETDATE(),NextRunTime) between 0 and 20
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 20)Future
Where Catalog.ItemID = FutureItemID
And Path not like '%' + Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','')
END