In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1

And the output of this SQL Query would be

In this article, we will see how to do Database Backup Compression in SQL Server 2012. To do that you can use SQL Server Management Studio and also you can use a Transact-SQL statement. So let's have a look at a practical example of how to do database backup with compression. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

To visually create a Database Backup

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Select database then right-click on the selected database then click on "Task" then click on "Back Up".

This will open the following window:

To specify the destination of the database, select the destination then click on the Add Button. We then see a new dialog box, click on the browse button to choose the location

Now next click on "Options" and the following screen appears:

Now select Compress Backup.

The above image contains three options to select:

1. The first option, "Use the default server settings" tells the backup to use the server's default backup compression setting. By default, it is set to have backup compression off.
2. The Second option "Compress Backup" turns backup compression on.
3. The "Do not compress backup" option turns it off.

Now click again on the "General" option.

Now you will see that the database backup has completed successfully.

In this article I describe the LightSwitch development environment in Visual Studio 2012. We can easily create a LightSwitch application using Visual Studio LightSwitch 2012.

LightSwitch Development in Visual Studio 2012

Visual Studio LightSwitch automatically creates the User Interface for a DataSource without manually writing any code.

In order to begin our work with LightSwitch 2012 we will perform the following steps:

Step 1

1. Open the Visual Studio 2012.
2. Go to "File" => "New" => "Project..."
3. In "New Project" => "Installed" => "Template"
4. In "Template" => "LightSwitch"
5. Select "LightSwitch Application (Visual C#)".
6. Enter the Name and choose the location.
7. Click "OK".

Step 2

After creating the project a LightSwitch Designer will appear on the screen.

LightSwitch Designer consists of the following two options:

- we can create a new table for our application.
- we can also attach an external database. 

Step 3

Now we will see that Solution Explorer consists of a list of the following items along with two folders:

- Data Sources
- Screens

Here the "Data Sources" folder is used to store user data i.e. "Database Tables" and the "Screens" folder is used where we can create the user interface for our application.

Step 4

When we click on "Create new table" we will get the following image:

Each table consists of an "Id" that is automatically assigned and is also a "Primary Key" of the table.

Step 5

First we will rename the title of the table from "Table" to "ContactTable". Now we will add four additional columns called "Name", "Email", "Age" and "Address" to our table.

Step 6

In order to insert the record we need to create a User Interface screen. When we right-click on the "Screens" Folder we will click on "Add New." It will open the "Add New Screen" dialog window. As the window opens we will select "New Data Screen" from the Screen Template, we will also provide a Screen Name to our screen and choose the database table.

In order to continue we will Click on the "Ok" button. This will create a new UI screen for inserting a new data record by a user.

"Properties" are those where you set properties for various items.

Step 7

When we run the UI screen we get the following.

Step 8

Now we are able to insert a new record without writing any code. We don't need to create anything since Visual Studio LightSwitch automatically creates the screen for us with "Save" and "Refresh" buttons.

Step 9

In the following step we insert a record of another user; for that we create a new contact table.

Step 10

In Light Switch validations are automatically applied to the table. We do not need to apply any kind of validation on their own.

Deploy your LightSwitch application with only 3.00 EUR/month with HostForLIFE.eu

Here are some important points to consider while restoring a database backup. Let us first create a sample database named test using the following code.

A new database named test will be created in your server and data and log files are created in the d: drive.

Let us assume that you regularly take backup of this database using the below code:

backup database test to disk='d:\test.bak'

Now if you want to create another database or restore this test database to a new database named testing, you can use the following code

restore database testing from disk='d:\test.bak'

However executing the code above will give you the following error

Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_dat.mdf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_dat' cannot be restored to 'h:\test_dat.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_log.ldf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_log' cannot be restored to 'h:\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error occurs because the data and log files are currently being used by the database test. So you need to give different names for those files while restoring, as shown below

restore database testing from disk='d:\test.bak'
with
move 'test_dat' to 'd:\testing.mdf',
move 'test_log' to 'd:\testing.ldf'

The above code will work fine and new database will be created with the name testing.

Just be aware of this point while restoring a backup of existing database!

HostForLIFE.eu was established to cater to an under served market in the hosting industry; web hosting for customers who want excellent service. HostForLIFE.eu – a cheap, constant uptime, excellent customer service, quality, and also reliable hosting provider in advanced Windows and ASP.NET technology. We proudly announces new SQL 2012 Reporting Services hosting in our entire servers environment.

SQL Server 2012 provides Mission Critical Confidence with greater uptime, blazing-fast performance and enhanced security features for mission critical workloads; Breakthrough Insight with managed self-service data exploration and stunning interactive data visualizations capabilities; Cloud On Your Own Terms by enabling the creation and extension of solutions across on-premises and public cloud.

SQL Reporting Service (SSRS) 2012 has several Key Capabilities:

- Immersive Interactive data visualization with Power View - Self-service reporting has never been so engaging
- Professional, Precisely Formatted Reports - Create pixel-perfect professional reports using familiar tools and interfaces.
- Robust Management and Scalability - Simplify reporting management and easily scale reports on-premises or in the cloud.

“With SQL 2012 Reporting Services, our customers have ability to manage their report in real time. We really proud that we can deliver the latest Microsoft reporting solution with an affordable price. As Microsoft SPOTLIGHT recommended partner, we have a very strong commitment to introduce the latest Microsoft product to the worldwide market.” Said John Curtis, VP Marketing and Business Development at HostForLIFE.eu.

For complete information about this new product, please visit our site at http://www.hostforlife.eu

About HostForLIFE.eu:

We are Microsoft No #1 Recommended Windows and ASP.NET Hosting in European Continent. Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and many top European countries. We support Microsoft technology, such as the latest ASP.NET 4.5, ASP.NET MVC 4, SQL 2008/2008R2/2012, Web Deploy, Visual Studio and much more.

Our number one goal is constant uptime. Our data center uses cutting edge technology, processes, and equipment. We have one of the best up time reputations in the industry.

Our second goal is providing excellent customer service. Our technical management structure is headed by professionals who have been in the industry since it's inception. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this tutorial, I will show you how to create SSRS report in ASP.NET.

Step 1: Create Dataset

Step 2: Create and Bind .rdlc file with Dataset.

Step 3: Add ReportViewer and call your .rdlc file.

So your .aspx file looks like this.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
    Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Test SSRS Demo by Jayendrasinh Gohil</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btndisplay" runat="server" Text="Display Report" OnClick="btndisplay_Click" />
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt"
            InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt"
            Width="908px" Visible="false">
            <LocalReport ReportPath="Report.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData"
            TypeName="testreportDataSetTableAdapters.Get_Address_spTableAdapter"></asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>

Step 4: New bind your report upon the button click event.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
string Connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           
        }
      
    }
 
   protected void btndisplay_Click(object sender, EventArgs e)
    {
        BindReport("Employee", 14);
    }
    private void BindReport(string p, int p_2)
    {
        SSRSReport report = new SSRSReport();
        SqlParameter[] sqlParams = new SqlParameter[] {
         new SqlParameter("@Add_Source_Id", 25) ,
         new SqlParameter("@Add_Source_Type", "Employee")
      };
        string ReportDataSource = "DataSet1";
        bool bind = report.CreateReport(Connectionstring, "Get_Address_sp", sqlParams, ref ReportViewer1, ReportDataSource);
        if (bind)
        {
            ReportViewer1.Visible = true;
        }
    }

}

I created a class for binding the report, which is very important.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;

/// <summary>
/// Summary description for SSRSReport
/// </summary>
public class SSRSReport
{
   
 
       public SSRSReport()
       {
              //
              // TODO: Add constructor logic here
              //
       }

    public bool CreateReport(String Connectionstring,string StoreProcedureName ,SqlParameter[] Parameter,ref  Microsoft.Reporting.WebForms.ReportViewer ReportViewer,string ReportDataSource)
    {
        bool reportbind = false;
        using (SqlConnection con = new SqlConnection(Connectionstring))
        {
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = StoreProcedureName;
            com.Parameters.AddRange(Parameter);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(ds);
            ReportDataSource datasource = new ReportDataSource(ReportDataSource, ds.Tables[0]);
            if ( ds.Tables[0].Rows.Count > 0)
            {
                ReportViewer.LocalReport.DataSources.Clear();
                ReportViewer.LocalReport.DataSources.Add(datasource);
                reportbind = true;
            }                      
        }
       return  reportbind;
    }

}

Find Affordable SSRS hosting solution at HostForLIFE.eu

When choosing this solution, getting capabilities that provide flexibility as well as control via technologies as well as applications recognized and reliable globally is crucial. Microsoft's worldwide reach as well as impact appears second to none producing the Hyper-V system a great choice for just about any virtual This infrastructure. Visitor operating systems operating in a Hyper-V atmosphere with VSC motorists and providers installed, feature performance standards rivaling that of a good OS operating on bodily hardware. Hyper-V continues to be designed to function seamlessly as well as efficiently along with Windows programs and providers as any additional innovative Ms Product.

Financial savings

Cloud processing increases the toughness for IT facilities and grows technology administration capabilities of the company with an off-site consulting professional. Increased sources, automated provisioning as well as maximized abilities offer versatility in controlling as well as distributing providers and funds where the organization needs this most. Growing processing energy, network data transfer, and storage space capacity is actually accomplished effortlessly. This versatility means a business can update or limit cloud energy as the organization grows or even market needs shift, maintaining IT costs low. There is no requirement of introducing and looking after IT employees as requirements change therefore the company will save on recruiting, instruction and pay-roll costs. Extra savings can be acquired by using home windows cloud host hosting suppliers that can offer Hyper-V impair server but additionally manage the actual servers for you personally.

Hyper-V Cloud Web hosting is a Eco-friendly Solution

Supplying global use of business programs increases employee’s efficiency as well as lowers environmentally friendly footprint of the business because travel as well as commuting needs reduce. Business analyst’s estimation enterprise this accounts for as much as 40% of a corporation's energy usage. Cutting back may generate a big decrease in just one organization's co2 footprint. Within 2006, the actual Department of Energy believed that Ough.S. Information centers taken into account approximately One.5 percent of U.Utes. Electricity utilization. Cloud processing allows greater utilization of Processor resources. Bringing together and discussing those sources helps stop the unfavorable effect of information center crowds and decreases greenhouse gasoline emissions.

Higher Availability Web hosting
Information as well as accessibility would be the keys to achievement in the worldwide marketplace. The actual infrastructure which hosts information and marketing communications must be housed in a reliable and dependable environment. Hyper-V Impair Hosting tends to make every element of the facilities fully repetitive. In the uncommon event a web server goes traditional, services aren't interrupted as well as in a completely clear and immediate process, failover happens keeping companies and workers at work. Impair hosting provides organizations use of an outsourcing flexible, scalable facilities at a lower cost compared to in-house. Energy needs for companies and knowledge centers tend to be minimized, decreasing their co2 footprint. Higher availability, completely redundant according to N+1, load well balanced networking can be obtained to tiny as well as business accounts, producing Hyper-V Cloud web hosting the optimal facilities as a support solution.

Reseller Choice Home windows Cloud Machines offers fast deployment easy to customize virtual situations within a extremely available Home windows Cloud Web hosting environment. The automated procedures allow you to set up cloud host instances quickly as well as alter the configurable choices which make this particular offer really elastic as well as tailored for your present requirements. Your digital instance includes your selected equipment configuration, uncovered operating system as well as full admin privileges. Your own virtual example will include your best hardware settings, bare operating-system and complete administrative rights.

As you may know, you cannot detach a mirrored database or bring it offline to move a data or log file from one drive to another drive. Moving a database file for a mirrored database is not the same as moving a normal database. Here I will show you the step by step process on how to move the data and/or log file(s) from one drive to another drive with minimum downtime.

Solution

Moving database files can be done two ways; by detaching the database then moving the database file(s) to the target location and then attaching the database from the new location. The other option is to run an ALTER statement to change the file location in the system catalog view, bring the database offline, then copy the file(s) to the target location and bring the database online. With database mirroring enabled for the database, both options will fail because your database is mirrored. We can't detach the mirrored database, nor can we bring it OFFLINE.

Here is step by step solution to reduce your downtime and move your database file from one location to another location for a mirrored database.

Steps

Step 1
Check the database files location for all database files.  Here we are going to move database "NASSP2".

sp_helpdb NASSP2

Here we can see two database files placed on the C: drive. As per best practice, we should not place database files on the system C: drive, so this is what we are going to move.

Step 2
Check the database mirroring configuration for your database. Run the below script to check the database mirroring status and its current partner name.

SELECT (SELECT DB_NAME(7)AS DBName),
database_id,
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring
WHERE database_id=7

We can see the mirroring_role_desc for this server is principal and its partner/mirrored instance name.

Step 3
If the database file size is big it will take some time to copy from one drive to another drive. So to over come this issue and minimize the downtime, we will failover our database from our principal server to its MIRROR server and route our application to the new principal server (earlier mirrored box) to bring the application online and run business as usual. Run the below command to failover this database.

ALTER DATABASE NASSP2 SET PARTNER FAILOVER

Step 4
Now we can again check the database mirroring configuration to see the current mirroring state. Run the same script which we ran in step 2. This time the output is the same, except one column. Here mirroring_state_desc is MIRROR where earlier it was principal.

Now our principal instance has become mirrored. Ask your application team to change the ODBC configurations and route the application connection to the new principal server. Now we can do make changes without downtime being of any concern. Note that if you have databases in a shared environment, then you may need to failover all databases to the mirrored server to reduce any downtime.  The technique requires stopping the database services, so this could impact other databases on this server.

Step 5
As we saw in step 1, two database files are on the C: drive. Now we have to move these two database files from 'C:' to 'E\MSSQL2008\DATA' drive. First we need to run an ALTER DATABASE statement to change the file location in master database system catalog view. Make sure to run this ALTER statement for every file that needs to be moved to the new location.

ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_System_Data', FILENAME='E:\MSSQL2008\DATA\nassp2_system_data.mdf')
go
ALTER DATABASE NASSP2
MODIFY FILE (NAME='NASSP2_log', FILENAME='E:\MSSQL2008\DATA\nassp2_log.ldf')
go

Step 6
Now, stop the SQL Server instance to copy the data and log file(s) to the target location. I used PowerShell to stop and start the services. You can use services.msc utility or SQL Server Configuration Manager as well.

STOP-SERVICE MSSQLSERVER –FORCE

Check the status of SQL Server service.

GET-SERVICE MSSQLSERVER

Step 7
Now copy both database files (nassp2_system_data.mdf and nassp2_log.ldf) to the new target location ('C' to 'E:\MSSQL2008\DATA').

Step 8
Once both files has been copied, start the SQL Server services.

START-SERVICE MSSQLSERVER

Check the status of SQL Server service

GET-SERVICE MSSQLSERVER

Step 9
Once SQL Server has started, failback your database from current principal to your primary box. Run step 1 again to check the location of the files and run step 2 again to check the mirroring status.

This article going to talk about real world query optimization.Most of the times all developers and Database administrators face the long time running query.so this article will help to you optimize the sql query with index.

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

Table columns: 

tblEmail (email varchar(255), IsDeleted int)
tblEmailPromotion (email varchar(255), PromotionID int)

Both tables without index,

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 2:43 (2 minutes 43 seconds) to get the result.

Change the question from "LEFT OUTER JOIN" to "IN" become

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

Now, you’ll see the different, it will takes faster result.

Now, we index table tblEmail column Email (Unique)

SELECT TOP (90) e.Email FROM tblEmail e
LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID
= 6
WHERE pe.PromotionID IS NULL
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It takes about 1:22 (1 minute 22 seconds) to get the result

Now, we run the 2nd query:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE
pe.PromotionID = 6)
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

It only takes 27 Seconds

To make the 2nd query better:

SELECT TOP (90) e.Email FROM tblEmail e
WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')
AND e.IsDeleted = 0
AND e.Email LIKE '%hotmail%'
ORDER BY e.Email

This will give 15 seconds.Now you can feel the different way of query optimization and retrive records from database in short time.

Hope the tutorial above help you. If you’re looking for SQL 2012 hosting on Europe server, please visit HostForLIFE.eu. HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.microsoft.com/web/hosting/HostingProvider/Details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.