October 19, 2009 at 8:40 am
There are several databases which restore throughout the day on the (2000) server, that cause an error in some SSRS reports. The error is Query Executuion failed for data set 'x' Navigate to report server on the local server machine...
Translated into an SQL error, this means...
Could not find database ID 'x'. Database may not be activated yet or may be in transition.
It happens because the database ID has changed during restore and the stored proc behind the report is looking for the old database ID. As a (temporary!) quick fix, I just drop and recreate the offending procs and it works fine.
What is the best method of prevention for this? I assume I don't want to have to use WITH RECOMPILE for each proc due to performance issues.
October 23, 2009 at 10:47 am
Database ID doesn't change with a restore, unless you are dropping it before the restore. Try to do a RESTORE WITH REPLACE to overwrite the current database with the backup.
Also, you can get the new database id with this:
SELECT DB_ID('dbname')
October 28, 2009 at 9:33 am
This restore does drop the old DB and restores the new one...unfortunately I can't alter the restore method.
October 28, 2009 at 9:46 am
robin-892784 (10/28/2009)
This restore does drop the old DB and restores the new one...unfortunately I can't alter the restore method.
I have to ask, but why can't you modify the restore method? It obviously is causing problems that requires dropping and recreating procedures on a regular basis.
October 28, 2009 at 9:56 am
This method is used to restore all of the DBs and is in a procedure I cannot edit. It is a reporting server where each DB is restored alongside the existing one and the existing one is hten deleted and the new one renamed to replace.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply