Tooting my own horn

So I had a developer run up to me yesterday and exclaim "Dude, you have to check SQL Server Central out, they have an article about that thing you fixed like a year ago"

Seeing that I fix lots of things I had no idea... so I looked. It wasn't quite a year ago, it was 2/10/2009 that it went into production, I never thought I was doing anything advanced.. so here goes.. I'm gonna be posting more of my stuff in the future!

http://www.mssqltips.com/tip.asp?tip=1846&home


It is about SSRS reports and being able to re run them. I created a little SSRS report that shows the reports (and their status, it also has a link that will call the re-run SP in this post)

I know there is some ugliness here but here goes the code, it has been in production for a while with no mods!

/*******************************************************************************************************************
* Program..........: SSRS_ScheduledReportsStatus
* Author...........: Ted E. Schoenling
* Version Added....: NA
* Created..........: 02/10/2009
* Description......: show SSRS Reports based upon status
* Defect...........:
* Table Affected...:
********************************************************************************************************************/


CREATE procedure [dbo].[SSRS_ScheduledReportsStatus] @Owner varchar(100) = null, @Failed bit, @reportname varchar(425) = null
as
BEGIN
declare @WhereFailed varchar(10), @WhereReportName varchar(500)

IF isnull(@Owner,'Everyone') = 'Everyone'
SELECT @Owner = '\%'

IF isnull(@Failed,0) = 1
BEGIN
select @WhereFailed = '%Fail%'
END
ELSE
BEGIN
select @WhereFailed = '%%'
END

IF ISNULL(@reportname,'All') = 'All'
BEGIN
select @WhereReportName = '%%'
END
ELSE
BEGIN
select @WhereReportName = +RTRIM(ltrim(@reportname))
END

select
'SubnDesc' = s.Description,
'SubnOwner' = us.UserName,
'LastStatus' = s.LastStatus,
'LastRun' = s.LastRunTime,
'ReportPath' = c.Path,
'ReportName' = c.Name,
'ReportModifiedBy' = uc.UserName,
'ScheduleId' = rs.ScheduleId,
'SubscriptionId' = s.SubscriptionID,
'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CONVERT(varchar(200),s.SubscriptionID) +'''' AS execString
from ReportServer.dbo.Subscriptions s
LEFT OUTER join ReportServer.dbo.Catalog c
on c.ItemID = s.Report_OID
LEFT OUTER join ReportServer.dbo.ReportSchedule rs
on rs.SubscriptionID = s.SubscriptionID
LEFT OUTER join ReportServer.dbo.Users uc
on uc.UserID = c.ModifiedByID
LEFT OUTER join ReportServer.dbo.Users us
on us.UserID = s.OwnerId
LEFT OUTER join msdb.dbo.sysjobs j
on j.name = CONVERT(nvarchar(128),rs.ScheduleId)
WHERE us.UserName like @Owner and s.LastStatus like @WhereFailed and c.Name like @WhereReportName
Order By 2,3,4,1
END


GO


/*******************************************************************************************************************
* Program..........: ReRunSSRSReportBySubscriptionID
* Author...........: Ted E. Schoenling
* Version Added....: NA
* Created..........: 02/10/2009
* Description......: Re run SSRS Reports by the subscription id
* Defect...........:
* Table Affected...:
********************************************************************************************************************/


CREATE Procedure [dbo].[ReRunSSRSReportBySubscriptionID] @SubscriptionID varchar(100)
as
begin
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@SubscriptionID

if @@ERROR = 0
begin
select 'report re-run'
end
else
begin
select 'Error re-running report'
end

end

GO