/******
SCRIPT: UpdatePaths
Created: May 25, 2010
Created By: Mark Terry
Updated: June 18, 2010
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 + ' ' + Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','') to Set Path = Path + ' ' + Whatever you want as long as you update the condition in the first update statement to match
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 15
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 15))
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 15
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 15)Future
Where Catalog.ItemID = FutureItemID
And Path not like '%' + Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','')
END
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)