Getting Started With SQL Server Reporting Services (SSRS) – Part 4

Overview

Hi Friends! This is fourth article of the series Getting Started With SQL Server Reporting Services (SSRS). In previous three articles we learned following

• How to create Simple Report using SQL Server Reporting Services?
• How to show data in Table Control from database?
• How to create parameterized reports?
• How to set visibility for report controls?
• How to show chart in reports?

For those who have not read previous articles they can read it from these links :

Getting Started With SQL Server Reporting Services (SSRS) – Part 1
Getting Started With SQL Server Reporting Services (SSRS) – Part 2
Getting Started With SQL Server Reporting Services (SSRS) – Part 3

Now, Some times we need to display data of multiple report into one single report. To achieve this instead of creating complete new report, all datasets we can use SubReport Control of SSRS to add multiple sub reports in a main single report. In this article we will learn how to use SubReport Control in our report to reduce our designing and development work. I hope if you are reading all articles of this series then you are almost used to with my Report Project 1 Solution.

So let’s start with our previous Report Server Project 1 Solution.

Advertisements

6 Things You Should Know About SQL Server 2016 Always On Encryption

The first public preview of SQL Server 2016 is now available for download. It is the biggest leap forward in Microsoft’s data platform history with real-time operational analytics, rich visualizations on mobile devices, built-in advanced analytics, new advanced security technology, and new hybrid cloud scenarios. SQL Server 2016 release promises many new features including a “Stretch” feature which allows you to automatically archive older data to the cloud, enhanced in-memory OLTP functionality, and several new enhancements in security.  One of the most interesting new security features is Always On Encryption.  Here are 6 things you should know about this feature.

1. Data is encrypted at all times
   Okay, so this might seem obvious but lets look at what this really means.  In the diagram above you see that the data for one or more columns of a table is stored in an encrypted state.  When SQL Server acts on this data locally it acts only on the encrypted version.  It never decrypts it and so it’s encrypted in memory as well as on the wire as it transits the network (or Internet) on the way to the client.  SQL Server treats the encrypted data as if it were the raw field.  Only at the point where the data reaches the client is it decrypted for use in your applications.  This makes the encrypted data nearly impervious to man-in-the-middle attacks or file based decryption on the server.
   

Difference between Stored Procedure and Function in SQL Server

This article will take you closer to Stored Procedures and functions. Some of you may be already familiar with these two most commonly used terms in SQL Server. This article will explain difference between Stored Procedures and functions. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.

Basic Difference

1. Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8. We can go for Transaction Management in Procedure whereas we can’t go in Function.

Summary

In this article I try to explain the difference between Stored Procedure and Function. I hope after reading this article you will be able to understand BASIC as well as ADVANCE difference between Stored Procedure and Functions (UDF). Comments, criticism and suggestions are always welcome.

How to connect with different types of DataBase

Introduction

While developing Ado.Net Applicaions most of the errors comes because of the wrong format of Connection String used to connect the application with the DataBase. I am publishing some Connection String formats to connect with different types of DataBase.

Connection String Formats

To connect with the Ms-Access 2003 DataBase Connection String Format:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Full Path of the DataBase File with Extension (".Mdb");Persist Security Info=True;Jet OLEDB:Database Password=********

To connect with the Ms-Access 2007 DataBase Connection String Format:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Full Path of the DataBase File with Extension (".accdb");Persist Security Info=True;Jet OLEDB:Database Password=********

To connect with the SQL Server DataBase Connection String Format:

DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataBaseName.mdf;
Integrated Security=True;User Instance=True

To connect with the MYSQL DataBase Connection String Format:

DataSource=Sever Name;DataBaseName=DataBase File Name;
UserName="Name";Password=*******

To connect with the Oracle DataBase Connection String Format:

Data Source=MyOracleDB;User Id=myUsername;Password=********;
Integrated Security=no;

I hope this will help New developers in ADO.Net Programming.

Read more about connection strings : http://www.connectionstrings.com/

SQL – Basic Syntax

SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax:

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names. So if you are working with MySQL, then you need to give table names as they exist in the database.

SQL SELECT Statement:

SELECT column1, column2....columnN
FROM   table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN
FROM   table_name;

SQL WHERE Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

SQL AND/OR Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

INDEX Constraint in SQL

The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created, it is assigned a ROWID for each row before it sorts out the data.

Proper indexes are good for performance in large databases, but you need to be careful while creating index. Selection of fields depends on what you are using in your SQL queries.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns:

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

Now, you can create index on single or multiple columns using the following syntax:

CREATE INDEX index_name
    ON table_name ( column1, column2.....);

To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL syntax:

CREATE INDEX idx_age
    ON CUSTOMERS ( AGE );

DROP an INDEX Constraint:

To drop an INDEX constraint, use the following SQL:

ALTER TABLE CUSTOMERS
   DROP INDEX idx_age;

CHECK Constraint in SQL

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn’t entered into the table.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL CHECK (AGE >= 18),
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS
   MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );

You can also use following syntax, which supports naming the constraint in multiple columns as well:

ALTER TABLE CUSTOMERS
ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);

DROP a CHECK Constraint:

To drop a CHECK constraint, use the following SQL.

ALTER TABLE CUSTOMERS
   DROP CONSTRAINT myCheckConstraint;