Google+ Followers

Friday, June 19, 2015

Export SQL Server Reporting Services Report Data into Multiple Excel with Worksheets

Problem
Sometimes SQL Server Reporting Services users want to export SSRS reports into Excel with multiple sheets, but when the SSRS report data region (Tablix/Matrix) doesn't have any grouping then all the data will be exported into a single sheet. How can we export a SSRS report into Excel with multiple sheets?
Solution
In this tip we will first review the problem and then demonstrate the solution. To demonstrate, I will use theAdventureworksDW2008R2 sample database and SQL Server 2012 Reporting Service.
This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

Wednesday, June 17, 2015

Restore SQL Server System Database master.mdf without Backup


Problem:
One of the biggest mistakes that most of the DBAs do is backing up their user database but not the system databases. May be, they cannot correctly predict the importance of system database or the smooth functionality of SQL Server never made them confront the need to backup this database. For any excusable reason, if backup for system database is not maintained, here is a tip shared that will help to get back the master database back even if no backup is available.
Solution:
First of all, why master database is so important that it must be backed up. This database stores all system–level information of the server. For example: The login info, the servers linked together, and other server level objects are stored in this database. This database can get into an inconsistent mode due to varied reasons that include hardware or software related issues which may restrict the server to get up and render its services.
In the later sections, I will be covering solution to restoring a damaged master database via following mediums:
#: Backup of master database is available and it has to be restored following the RTO challenge. The restoration of this DB is considered risky because it comprises of information about other databases and is a crucial component for starting up the database.
To get started, it is important that SQL Server single-user mode is activated. For that, open ‘SQL Server Configuration Manager’ and click on ‘Startup Parameters’ tab. In the text box for startup parameter, enter “-m” flag and click on ‘Add’ button. Click Apply and restart SQL Server.

To get the single user mode activated, the services has to be stopped and restarted again.


Now, for restoring the database through SQL Server Management Studio, the process is simple. Select the system database that has to be restored, right click on it, choose ‘Tasks’>> ‘Restore’>> ‘Files and Filegroups…’.

You will be presented with two different pages: “General” and “Options”. Select them and make relevant modifications in settings for restoration.




#: Rebuild Master Database and Restore it from Backup.If a workable master database is not available, it has to be first created. If a master database is already available, then the scenario and solution shared above will work. There are two methods that can be adopted for rebuilding the database.
1)    Use the Setup for Re-Creating the Master Database
In the process of rebuilding the master database, it is important to know that the resultant will have three databases created:master, model and msdb. For SQL Server 2012, there is no need for setup DVD/ISO. Run the following command from the bootstrap directory (C:\Program Files\Microsoft SQL Server\<SQL Version>\Setup Bootstrap\<Release>).

Syntax Details:


The values for these parameters should be replaced accordingly. Before running the command, make sure that a healthy copy of model and msdb database is saved as safe location.

Now when the master database is in a workable state, it can be recovered from the available backup. For this, it is important that all related services should be stopped and the empty msdb and model DB is replaced with the copy that is saved at safe location.

2)    Use the Template Master Database
In SQL Server 2012, the system database recovery criteria is a little different. There is template database that gets created at the time of installation. When the setup.exe is run for rebuilding the database, the template database is replaced over the database and transaction logs.
However, in case the msdb and model database are in working state, then it is a simpler job to just copy the template files to master database in spite of going for the long procedure of rebuilding the database and then restoring the msdb and model DB from backup. The template files are located at following location:
If master database backup is available, then restoration instruction can be checked out in section 1. But if there is no backup available, then section 3 will give an idea to deal with the problem.
#: The master database is unusable and there is no backup available. In this situation, what can be done is the master database can be reconstructed. For this, first of all connect to Server using SQL Server Management Studio and the admin account that was used while installation process or while rebuilding (in the above procedure). Here, you will observe that on expansion that databases tree is blank. This is because of the reason that master database stores the location info of all DBs which is now lost.
But it has to be understood that the database is still available, they are to be relocated. If the backup of the databases is there, then the job is easy, but if it is not, then the location of the database has to be recorded and attached manually. For this, the following T-SQL code can be used:

Note: It is necessary to change the database ownership as they must be owned by the account that has re-attached them.

Nevertheless, now a days, the idea of restoring system backups have taken a great turn in the way that the process has been simplified to a great extent. MDF file recovery programs for getting back the data from system databases have made restoration without backup very easy. The plus point is, for recovery of single database, others are not supposed to be involved like in the manual procedures. Most of the DBAs depend upon manual fixing in normal scenarios, but yes, these tools have made an impressive entry to deal with major issues in less RTO.
System Objects Restoration
On rebuilding the master database or on restoring it from the template, it is possible that some of the information like linked databases, login details etc. are lost. To get some of the objects back, you can take help of SSMS to create scripts. Although you won’t get the objects with details in exact match, but there is a fair possibility of getting very close to it.

 



Thursday, June 11, 2015

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?


This tip assumes that you have previous experience building a simple SSRS Report.
In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.
I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Click here to read complete article.

Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.

Friday, June 5, 2015

Set Select All as Default for Multi-Value Report Parameters in SQL Server Reporting Services

Most SQL Server Reporting Services (SSRS) reports contain multi-value report parameters and sometimes there be a requirement to set "Select All" as the default. There is not a simple way to set "Select All" as the default for an SSRS report, but in this tip I will demonstrate how to set "Select All" as the default parameter value.


Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report with parameters. I have prepared this tip in such a way that a SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.