Welcome to Julian Kuiters Friday, October 19 2018 @ 09:36 AM AEDT

Enable Remote Errors in SQL Server Reporting Services 2005

Reporting Services 2005

Enabling remote errors in SQL Server Reporting Services 2005 (SSRS)

By default, reporting services returns the following error message when a report produces an error:

An error has occurred during report processing.
Cannot create a connection to data source 'AdventureWorks'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors.

Its inconvenient and sometimes not possible to have to browse the page on the local server. In a development environment it would be better to enable remote errors.

To enable remote errors in SSRS create a .rss script ( a plain text file ) with the following contents:

Public Sub Main()
     Dim P As New [Property]()
     P.Name = "EnableRemoteErrors"
     P.Value = True
     Dim Properties(0) As [Property]
     Properties(0) = P
         Console.WriteLine("Remote errors enabled.")
     Catch SE As SoapException
     End Try
End Sub

Then run the script using the rs.exe utility that is installed with Reporting Services:

rs -i ssrs2005-enable-remote-errors.rss -s http://localhost/ReportServer

You can download the ssrs2005-enable-remote-errors.rss script here

Thanks to Robert Bruckner for the script.

Moving Reporting Services 2005 using RSConfig

Reporting Services 2005

Moving Reporting Services 2005 database from one SQL Server Instance to another

The development environment I'm currently working in uses two servers. IIS with Reporting Services 2005 on box1, and SQL Server 2005 on box2.

For whatever reason, I was unable to get Reporting Services to install correctly on box1 with box2 as the SQL Server instance. To save hassles I went ahead an installed an instance of SQL Server 2005 beta-2 on to box1 at the same time as Reporting Services.

Now that IIS has been configured, and Reporting Services has been tested that it works, I'm ready to move the databases from box1 to box2.

Moving the databases is a simple step. Stop the Reporting Services service by typing the following into command prompt:

net stop "reporting server (MSSQLSERVER)"
(or use the Services console from Start / Control Panel / Administrative Tools / Services). Then using SQL Management Studio, select the ReportServer database, and detact. Detach the ReportServerTempDB also. Copy the database *.mdf and *.ldf files to box2, and attach. (I copied the files, incase I needed to go back for some reason).

Now that the databases are residing on the correct SQL Server, it's time to reconfigure Reporting Services. Luckily Microsoft has thought of this already and provided us with the RSConfig command line configuration tool.

The rsconfig tool is pretty basic to use and really only has two uses: to change the connection info that a report server uses to connect to its database; or to change the account that report server uses to connect to a remote database server when other credentials are not available.

The syntax of the rsconfig command is:

rsconfig {-?}
{–c }
{–e }
[–m computername]
[–s servername]
{–d databasename}
{–a authmethod}
[-u username]
[-p password]
[-t ]

Before using the rsconfig tool however, you should take a backup of your server, or at least a copy of the rsreportserver.config file (which for me was located at c:/program files/Microsoft SQL Server/MSSQL.2/Reporting Services/ReportServer/rsreportserver.config)

So for me, the following configured Reporting Services just fine:

rsconfig -c -s box2 -d ReportServer -a Sql -u MSReportUser -p **************

Start the Reporting Services service again with:

net start "reporting server (MSSQLSERVER)"
(or use the Services console from Start / Control Panel / Administrative Tools / Services)

You may need to restart the IIS Application Pool that Report Server is a part of as well.

If you get a message that your server is not activated, use the rsactivate Utility.