Reporting Services Subscriptions

We recently migrated from Reporting Services 2000 to 2008 using the excellent (and free!) Reporting Services Scripter. However, it did not migrate the subscriptions. This leads to a problem: There is no way within the front end to tell what reports have subscriptions against them, especially if the subscriptions have been created by multiple people.

I therefore wrote the following SQL to get a list of subscriptions. The number of subscriptions returned was fairly low (30), so I could then manually recreate them on the new server.



select
     c.path ReportPath,
     c.name ReportName,
     u.UserName,
     s.*
from reportserver.dbo.Subscriptions s
inner join reportserver.dbo.users u
     on s.ownerid = u.userid
inner join reportserver.dbo.catalog c
     on s.report_oid = c.ItemID
order by
     c.path