SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Subscription Dynamic File Names


Report Subscription Dynamic File Names

Author
Message
markterry
markterry
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 28
Comments posted to this topic are about the item Report Subscription Dynamic File Names
markterry
markterry
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 28
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



markterry
markterry
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 28
The code I have written works well for my scenario but you may want to change it for yours. The main point is you need to change the path in the catalog table before the emails goes to what you want the filename to be, and you need to change it back afterwards so you can access the report via Report Manager. Other ways I thought of doing this was to try and hunt down the procedure called to run the report and change it before and after that code in that procedure. If you end up doing this please post so other can see how.
UKGav
UKGav
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 251
I did manage to find one article showing you how your could make a stored procedure to actually execute the report from, providing the parameters. However this only worked for one parameter, and was kind of a pain for a long term solution.


Please could you post your source for this?

- Gav B.
markterry
markterry
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 28
Dynamically name report/file export in SSRS
by Jason Selburg, 2010/03/22
Article: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69546/
Discussion: http://www.sqlservercentral.com/Forums/Topic887162-304-1.aspx
gcbohmann
gcbohmann
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 68
Has anyone else had success with this method? I always receive an Item not found error when the subscription runs if the path is altered in any way on SSRS 2008.
hkflight
hkflight
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 166
Hi Mark,

In your post, you mentioned that for file based subscriptions you can simply include @TimeStamp in the file name field. I am trying to include a parameter in the filename using @ParameterName, but it simply appends '@ParameterName' to the file and does not use the parameter value.

I am using SSRS 2008 R2. This would be a huge help. I am essentially trying to append an ID to each report and store it on our fileserver as a PDF. These are historical reports that only need to be created once a year.

Thanks!
markterry
markterry
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 28
Only @TimeStamp and a couple other parameters like @ReportName work in that field.

If you want to use a parameter to edit the file name, use my method and it should work. But it has been a while since I have looked at this stuff so maybe I am forgetting something.
UKGav
UKGav
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 251
I would be awesome if you could include @ParameterName in subscriptions or filenames ... Come on Microsoft, make it so...
hkflight
hkflight
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 166
I am going to try SQL 2012 to see if it is a new feature.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search