Tag Archives: SSRS

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'

Select All in Reporting Services

A common request when having a parameter as a dropdown in SSRS, is a “Select all” entry at the top of the list. If you you use a multi-value parameter in SQL Server 2005 SP0 or SP2 (Not available in SP1), the top checkbox will have this option. But if you don’t want to make the parameter multi-value, or you’re using SSRS 2005 SP1 or SSRS 2000, you have to do it like this:

Parameter dataset:

SELECT * FROM 
( 
SELECT Vendor.VendorID,Vendor.VendorName FROM Vendor 
UNION ALL 
SELECT -999 AS VendorID, 'All Vendors' AS VendorName 
) AS Tbl1 
ORDER BY CASE WHEN VendorID=-999 THEN ' ' ELSE VendorName END

Notice the order by clause. This makes sure that “All Vendors” appear at the top of the list.

For the main dataset:

SELECT * FROM Sales WHERE Vendor=@VendorParam OR @VendorParam=-999