Tag Archives: Reporting

Delete all Subscriptions in Reporting Services

Yesterday I spent some time trying to write a script that deletes all schedules, subscriptions and related SQL Server agent jobs from a reporting services instance by using cursors and stored procedures in the msdb and Reportserver databases.

Turns out that the reportserver tables have the necessary triggers on delete, so all you need to do is a simple

DELETE FROM ReportSchedule
GO
DELETE FROM Subscriptions
GO
DELETE FROM Schedule
GO

Migration from SSRS 2005 to 2008

I just did a migration from a SSRS 2005 installation to SSRS 2008 instance on a different server. Microsoft has very nice guides about the migration process that I followed, but I encountered problems when trying to restore the encryption key on the new server. I tried to post the question to the msdn message board here, but got no response.

I’m currently trying to move a ssrs 2005 installtion to a sql server 2008 instance on another physical server. I’ve followed every step on the official migration guide, and the database is upgraded and all data is there. The problem is when I try to restore the encryption key on the 2008-server. The old server then shows up under “scale-out deployment”, and is impossible to remove. If I go to the 2008 report manager, I get “The feature: “Scale-out deployment” is not supported in this edition of Reporting Services”. If I try to remove the old server from the “scale-out deployment”-tab, I get: “Microsoft.ReportingServices.WmiProvider.WMIProviderException: No report servers were found. I tried to delete all encrypted data on the 2008 server, and that made the 2008-instance work, but I have 150 subscriptions on the old 2005-instance that I don’t want to recreate (understatement).

The solution was to use the rskeymgmt utility by command line interface when restoring the key:

rskeymgmt -i NEWINSTANCE -a -f FILEPATH -p PASSWORD

If you use the configuration tool, the restored key will be linked to the wrong instance, and the old server will show up as a scale-out deployment server.

UPDATE:

Its easier to just just do the following after you restore the key using the configuration tool:

USE ReportServer
DELETE FROM Keys WHERE MachineName='OLDMACHINENAME'