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"

Article translations Article translations
Article ID: 946741 - View products that this article applies to.
Bug: #50002134 (SQL Hotfix)
Expand all | Collapse all

On This Page

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:
http://msdn2.microsoft.com/en-us/library/ms143724.aspx
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') = 1
begin
	ALTER TABLE [dbo].[SessionData] ALTER Column [SnapshotDataID] uniqueidentifier NULL
end
GO

if (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'IsPermanentSnapshot') = 1
begin
	ALTER TABLE [dbo].[SessionData] ALTER Column [IsPermanentSnapshot] bit NULL
end
GO
	
if (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'ReportPath') = 1
begin
	ALTER TABLE [dbo].[SessionData] ALTER Column [ReportPath] nvarchar(424) NULL
end
GO

if (select count(*) from dbo.syscolumns where id = object_id('SessionData') and name = 'HasInteractivity') = 1
begin
	ALTER TABLE [dbo].[SessionData] ALTER Column [HasInteractivity] bit NULL
end
GO

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:
http://msdn2.microsoft.com/en-us/library/ms143724.aspx

Properties

Article ID: 946741 - Last Review: January 10, 2008 - Revision: 1.3
APPLIES TO
  • Microsoft SQL Server 2005 Reporting Services
Keywords: 
kbtshoot kbsql2005rs kbexpertiseadvanced kbprb KB946741

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com