You are currently offline, waiting for your internet to reconnect

Error message when you display a report after you migrate a SQL Server 2000 Reporting Services deployment directly to a new instance of SQL Server 2005 Service Pack 2 Reporting Services: "An internal error occurred on the report server"

Bug: #50002134 (SQL Hotfix)
SYMPTOMS
You migrate a Microsoft SQL Server 2000 Reporting Services deployment directly to a new instance of Microsoft SQL Server 2005 Reporting Services Service Pack 2 (SP2). To do this, you use the method that is described on the following Microsoft Developer Network (MSDN) Web site:Then, when you display a report, you receive the following error message:
An internal error occurred on the report server. See the error log for more details. (rsInternalError)
Cannot insert the value NULL into column 'SnapshotDataID', table 'ReportServerTempDB.dbo.SessionData'; column does not allow nulls. INSERT fails. The statement has been terminated.
Note This issue does not occur if the following conditions are true:
  • You migrate SQL Server 2000 Reporting Services to the release version of SQL Server 2005 Reporting Services or to SQL Server 2005 Service Pack 1 (SP1).
  • Then, you migrate to SQL Server 2005 SP2.
CAUSE
This issue occurs because the ReportServerTempDB database is not correctly migrated if you migrate SQL Server 2000 Reporting Services directly to SQL Server 2005 SP2.
WORKAROUND
To work around this issue, use one of the following methods.

Method 1

After you migrate SQL Server 2000 Reporting Services directly to SQL Server 2005 SP2, run the following statements against the ReportServerTempDB database.
if (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'SnapshotDataID') = 1begin	ALTER TABLE [dbo].[SessionData] ALTER Column [SnapshotDataID] uniqueidentifier NULLendGOif (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'IsPermanentSnapshot') = 1begin	ALTER TABLE [dbo].[SessionData] ALTER Column [IsPermanentSnapshot] bit NULLendGO	if (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'ReportPath') = 1begin	ALTER TABLE [dbo].[SessionData] ALTER Column [ReportPath] nvarchar(424) NULLendGOif (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'HasInteractivity') = 1begin	ALTER TABLE [dbo].[SessionData] ALTER Column [HasInteractivity] bit NULLendGO

Method 2

Migrate SQL Server 2000 Reporting Services to the release version of SQL Server 2005 Reporting Services or to SQL Server 2005 Service Pack 1 (SP1). Then, install SQL Server 2005 SP2.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
For more information about how to migrate Reporting Services, visit the following Microsoft Developer Network (MSDN) Web site:
Properties

Article ID: 946741 - Last Review: 01/10/2008 17:08:05 - Revision: 1.3

  • Microsoft SQL Server 2005 Reporting Services
  • kbtshoot kbsql2005rs kbexpertiseadvanced kbprb KB946741
Feedback