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

The partition scheme "" is currently being used to partition one or more tables.

Couldnt drop rogue Partition Scheme due to rogue indexes.

Indexes were not visible through SSMS, but sp_help ‘tablename’ revealed a number of additional indexes, which were based on the partition scheme.

Drop the indexes using DROP INDEX, and rerun sp_help ‘tablename’. This should confirm there are no objects linked to the rogue partition schemes. The rogue partition schemes can then be dropped using DROP PARTITION SCHEME, before dropping the partition functions using DROP PARTITION FUNCTION.

To summarise:

Sp_help ‘tablename’

DROP INDEX IndexName ON TableName for all rogue indexes.

DROP PARTITION SCHEME PartitionScheme

DROP PARTITION FUNCTION PartitionFunction

DROP PARTITION FUNCTION PartitionFunction