I am working on upgrading our environment from Sharepoint 2007 to Sharepoint 2010. At this point I am performing test upgrades in a dev environment. Here is our current config: Sharepoint 2007, SSRS 2008 R2, SQL 2008 R2 all running on a single 2008 R2 Server. New config: SharePoint 2010 and SSRS 2008 R2 running on a virtual 2008 R2 Server and SQL 2008 R2 running on th original 2008 R2 Server. The appropriate Sharepoint SSRS addin is loaded on the servers. I am peformng a detach / attach upgrade so that I can keep our SP 2007 environment running during the upgrade. Upgrading the SP content databases, user profiles and search services all went smooth and run fine. My problem is when I try to migrate the SSRS databases. Here is what I have tried: Backed up existing ReportServer db's and encryption key. Installed SSRS on Sharepoint 2010 server and ran config to create a new report server databases. I then restored from backup over the top of these DB's and restored the encryption key. I then configured SSRS in Central Admin of the SP 2010 environment. When I do this, I get all sorts of errrors when running reports (ie: ReportServer Installation is not initialzed, cannot find the rdl files when I try to run the reports, etc.) I am not sharing the specific error messages because I first want to know what approach is the best. I then started over and uninstalled and reinstalled SSRS on the SP2010 Server. I created the new ReportServer db's, ran the SSRS Config and then setup Reporting Services in CA. So now none of the reports are availabe in my SP site, which is fine. I re-deploy my reports and datasources from BIDS and everything works fine. My question is simply this. Is the only way to migrate my SSRS environment from SP2007 to SP2010 is to create new reportserver db's and then re-deploy the reports and datasources and also recreate the subscriptions? If this is what I have to do, I am fine with that, but if someone knows of a better/easier way, I would appreciate some tips. Thanks, Tim Olig

Hi Tim, It seems your original steps are correct, by generally, what we need to do is just making a backup of the RS ReportServer and ReportServerTempDB databases from your SP 2007 environment, and you also took a backup of the RS encryption keys. Then, follow these steps: 1. Restore the RS database backup on a SQL Server database 2. Start the Reporting Services Configuration Tool (http://msdn.microsoft.com/en-us/library/ms159644.aspx) 3. Connect to the RS 2008 R2 instance configured in SharePoint integrated mode 4. Configure the database, and point it to the restored copies of the RS databases from your original RS 2008 SP-integrated deployment At that point the RS 2008 R2 service should attach, and automatically upgrade the database structures. Finally, don't forget to restore the encryption keys via the RS configuration tool. To your error message, it seems you did not initialze ReportServer Installation To confirm report server initialization, ping the Report Server Web service by typing http://<servername>/reportserver in the command window. If the RSReportServerNotActivated error occurs, the initialization failed. To initialize a report server, use the Reporting Services Configuration tool. Initialization occurs automatically when you create and configure the report server database. For more information about Steps to consider when Upgrading from SharePoint 2007 to 2010 with Reporting Services in SharePoint mode, please see: http://blogs.msdn.com/b/prash/archive/2010/05/26/steps-to-consider-when-upgrading-from-sharepoint-2007-to-2010-with-reporting-services-in-sharepoint-mode.aspx Thanks, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

I am also in the process of migrating/upgrading my Report Server and receiving the same errors as the OP My initial environment - Sharepoint 2007, SQL Server 2005 SP2 Target environment - Sharepoint 2010, SQL Server 2008 R2 Challen, I follow your steps without any issues 1. Restore Report Server databases from SQL 2005 - question do I need the Temp DB or will it be recreated? 2. Configure Reporting Services and point to restored databases - Report Server was already setup and pointed to databases prior, just to complete and verify the initial installation 3. Restored Encryption Keys Reporting Services config shows everything is good, database is in Integrated Mode 4. In Central Admin I create a new Web App, restore 2007 content database When I load up the site, everything appears to be ok until I start looking through the reports (40+), this is where I start seeing the same errors as OP - Cannot find RDL files when attempting to view reports, cannot find RSDS files when trying to edit Shared Datasources A handful of reports worked, but many did not I took a look in the report server logs and wow.. errors and more errors - it seems it's having trouble upgrading many of my report entries, stating duplicate key errors.. but why? library!WindowsService_4!884!07/27/2011-11:14:31:: i INFO: Upgrading SharePoint catalog paths. library!WindowsService_4!884!07/27/2011-11:14:31:: i INFO: Failed to create SPSite for: http://qarfmoss1:82/Reports/ReportsLibrary/ISR - Construction Remittance library!WindowsService_4!884!07/27/2011-11:14:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.; library!WindowsService_4!884!07/27/2011-11:14:31:: Error upgrading the items with name 'UpgradeSharePointCatalogPaths' library!WindowsService_4!884!07/27/2011-11:14:31:: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Catalog' with unique index 'IX_Catalog'. The statement has been terminated. 11-11:14:30:: i INFO: Failed to create SPSite for: http://qarfmoss1:82/Reports/ReportsLibrary/ISR - Construction Remittance Also it seems to be still referencing to the previous name of my TempDB: library!WindowsService_4!884!07/27/2011-11:14:29:: i INFO: Upgrading SharePoint schedule paths. library!WindowsService_4!884!07/27/2011-11:14:29:: i INFO: Failed to create SPSite for: http://qarfmoss1:82/Reports library!WindowsService_4!884!07/27/2011-11:14:29:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.; library!WindowsService_4!884!07/27/2011-11:14:29:: Error upgrading the items with name 'UpgradeSharePointSchedulePaths' library!WindowsService_4!884!07/27/2011-11:14:29:: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerSPmodeTempDB.dbo.ExecutionCache'. I have to manually go into the report server database, open the Catalog table, edit the record it is stuck on and give the path a slightly different name, then this seems to kick it back into action.. until it gets stuck a couple more reports later Sometimes I don't need to do this and can just attempt to open the Processing Options of the report, this seems to get it going Is there something I'm missing? Why is the upgrading getting 'stuck' like this and even still referencing back to a previous database name?

Just found something interesting as related to my errors where something seemed to be referencing back to an old database name - the one as was used in SQL 2005 I found the error was coming from a Trigger in the Schedule table, the trigger is set to run on Update of the table and references the ExecutionCache table in the TempDB.. well the database name is written directly into the Trigger and was not updated in the upgrade Is this a bug? I'm a bit worried what else might be in the database that has the database names hardcoded Correcting this trigger then allowed SQL to 'upgrade' the Path field, but then of course this didn't work correctly and instead updated every one with an invalid GUID : /{00000000-0000-0000-0000-000000000000} Schedules were still not showing up in Sharepoint, so I created a quick test schedule, checked the table again and it was there with the correct GUID: /{4f4d9e27-1fa5-4272-b04c-4b23378575f8}/Reports So I updated the others with the same, and presto now they are showing up in Sharepoint So why do I need to go through this trouble? What is wrong with the upgrade process?