Recently have updated my code as follows after a few errors at certain report subscription times:

/******

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