Skip to content

Posts from the ‘SSRS’ Category

Oct 22

2 Comments

Uninstalling SQL Server Reporting Services (SSRS) 2008R2 component

Here are the steps that you can follow, if you have to uninstall only SQL Server Reporting Services 2008 R2 from your server. These steps are pretty easy and can be completed without any issues. Here we will be uninstalling SSRS 2008 R2 from Windows 2008 R2 server.

STEPS

1) Navigate to control panel à Program and features. In Windows 2008 and earlier edition go to add/remove programs. Select Microsoft SQL Server 2008 R2 and click on change/uninstall.

2) You may see below error if other people are also logged in to this server. You may have to wait for other user to logoff from the server before continuing.

3) In this window click on Remove option.

4) You may see this window for some time. No action required, this step is just for FYI. Move to step 5.

5) Here installer will check the setup support rules. If there are no failures go to next step.

6) Select the instance for which you want to remove SSRS component. Here I have selected the default instance (as it is the only instance available in this example). Click Next.

7) Here in this window select the components to be removed. Here we will select Reporting Services and will move to next step.

8) Uninstall process will check for any processes which can block this uninstall and can lead to failure. If no failure is found proceed to next step.

9) Review the summary to make sure you have all the required components selected like here only SSRS, for uninstall. Click on Remove.

10) You will see below screen if SQL Server Reporting Services is uninstalled successfully.

NOTE: – Removing other SQL Server 2008 R2 components is also similar to what we have done above for removing SSRS.

Oct 16

SSRS service status is Change Pending

Ever seen SQL Server Reporting Services service is not getting started on time and the service status is Change Pending? Why SSRS service is not starting and stuck at Change Pending? Here we will talk about this issue, its possible cause and resolution.

Problem and possible issue

SQL Server Reporting Service is not getting started or taking too long to start and the service status is ‘Change Pending’

Mostly you will hit this issue after server reboot and when all the SQL Server services tries to start. Now for SSRS to start successfully, it needs ReportServer database to be online, but when SSRS tries to start it didn’t find this database online and got stuck at Change Pending. Status will move to started only when ReportServer database comes online.

Reason why this database didn’t get started on time may be because of resource issue on the server. If you hit issue more frequently, then you may have to think for hardware upgrade (run perfmon to do more analysis on server performance).

Analyzing Error Message

You will find below errors (may be multiple times) in the current log file of SSRS error logs located at ..\MSRS10_50.SQL2008R2\Reporting Services\LogFiles (.. is the base drive and/or directory path where SSRS is installed):-

library!DefaultDomain!448!09/29/2011-22:36:16:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. —> System.Data.SqlClient.SqlException: Cannot open database “ReportServer$SQL2008R2″ requested by the login. The login failed.

Login failed for user ‘MACHINE23AF\SQLServer’.

library!WindowsService_0!448!09/29/2011-22:36:19:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. —> System.Data.SqlClient.SqlException: Cannot open database “ReportServer$SQL2008R2″ requested by the login. The login failed.

Login failed for user ‘MACHINE23AF\SQLServer’.

Now if you analyze the SQL Server error log and tries to map it with SSRS errors log. You will find below entries around the time when errors are written in SSRS logs around same time:-

2011-09-29 22:36:12.09 Logon       Error: 18456, Severity: 14, State: 38.

2011-09-29 22:36:12.09 Logon       Login failed for user ‘MACHINE23AF\SQLServer’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

2011-09-29 22:36:12.21 spid13s     Service Broker manager has started.

2011-09-29 22:36:19.48 Logon       Error: 18456, Severity: 14, State: 38.

2011-09-29 22:36:19.48 Logon       Login failed for user ‘MACHINE23AF\SQLServer’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

2011-09-29 22:36:25.21 Logon       Error: 18456, Severity: 14, State: 38.

2011-09-29 22:36:25.21 Logon       Login failed for user ‘MACHINE23AF\SQLServer’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

2011-09-29 22:36:35.23 Logon       Error: 18456, Severity: 14, State: 38.

2011-09-29 22:36:35.23 Logon       Login failed for user ‘MACHINE23AF\SQLServer’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

2011-09-29 22:36:42.18 spid18s     Starting up database ‘ReportServer$SQL2008R2′.

2011-09-29 22:36:42.19 spid17s     Starting up database ‘msdb’.

2011-09-29 22:36:42.19 spid20s     Starting up database ‘ReportServer$SQL2008R2TempDB’.

2011-09-29 22:36:42.59 spid6s      Recovery is writing a checkpoint in database ‘ReportServer$SQL2008R2TempDB’ (6). This is an informational message only. No user action is required.

2011-09-29 22:36:43.75 spid6s      Recovery completed for database ReportServer$SQL2008R2 (database ID 5) in 1 second(s) (analysis 40 ms, redo 21 ms, undo 587 ms.) This is an informational message only. No user action is required.

Sep 29

Configuring firewall settings for SQL Server Reporting Services (SSRS) on Windows 2008 R2

This is the last of the series of articles that we have published on how to configure firewall settings for SQL Server components installed on Windows 2008 R2 server. So, now we will be talking about opening port for SOL Server Reporting services on Windows 2008 R2.

1) Go to Start –> Administrative tools–> Windows Firewall with Advanced security tool.

2) Here go to Inbound Rule tab and open a new rule window. We will be creating a new rule in order to open a port for SSRS.

3) Since we have to open port for SSRS, select the option Port in Rule type and move to the next step.

4) Select TCP and mention the port number as 80. Then move to next step.

5) Select allow the connection so that connections are allowed to SSRS using port 80. Then move next.

6) In the profile tab, select the appropriate option as per your requirement.

7) In this final step, provide appropriate name to this rule like ReportServer (TCP on 80) and click Finish.

8) Rules are enabled by default but you can verify that by going to Inbound rules and look for the newly created rule.