PIVOT function in SQL Server 2005


PIVOT:

This is one of the most important enhancements to TSQL in SQL 2005 and is extremely useful for generating reports based on cross tab values.The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way.


The syntax for the PIVOT relational operator is:


FROMtable_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN (<column_list>)
) table_alias


table_source : The table where the data will be pivoted.
aggregate_function : The aggregate function that will be used against the specified column. pivot_column : The column that will be used to create the column headers.
column_list : The values to pivot from the pivot column.


For Example:


create table parameter
(
pid int,
pname varchar(50)
)
GO


create table TransactionInfo
(
TransId int,
pid int,
pvalue int
)
GO


insert into parameter values(1,'Para1')
GO
insert into parameter values(2,'Para2')
GO
insert into parameter values(3,'Para3')
GO
insert into parameter values(4,'Para4')
GO
insert into parameter values(5,'Para5')
GO

insert into TransactionInfo values(1,1,30)
GO
insert into TransactionInfo values(1,2,40)
GO
insert into TransactionInfo values(1,3,50)
GO
insert into TransactionInfo values(1,4,35)
GO
insert into TransactionInfo values(1,5,45)
GO
insert into TransactionInfo values(2,1,50)
GO
insert into TransactionInfo values(2,2,60)
GO
insert into TransactionInfo values(2,3,70)
GO
insert into TransactionInfo values(2,4,85)
GO
insert into TransactionInfo values(2,5,45)
GO


In TransactionInfo table row wise records are stored. If you need columnwise report we go for PIVOT functions.

select TransID,para1,para2,para3,para4,para5 from
(select a.pname,b.pvalue,TransID from parameter a,TransactionInfo b
where a.pid = b.pid and TransID in (1,2) ) as aa
pivot
(
sum(pvalue)
for pname in([para1],[para2],[para3],[para4],[para5])
) as bb
GO


The Following result set is obtained after executing the above query, which PIVOTS data based on the parameters thats been selected.


Output of above query is as follows


SQL Server 2005 - INTERSECT and EXCEPT operands.



INTERSECT and EXCEPT operands:


Like Union and Union all SQL Server 2005 introduces INTERSECT and EXCEPT operands.


INTERSECT :

It is useful for retrieving only distinct rows that exist in both the left and right queries.

EXCEPT :

It is useful for retrieving only distinct rows that exist in the left query.


For example :

Create table DeptA

( DeptId int, DeptName varchar(40))

GO

Create table DeptB

( DeptId int, DeptName varchar(40))

GO

insert into DeptA values(10,'Sales')

GO

insert into DeptA values(20,'Service')

GO

insert into DeptA values(30,'Puchase')

GO

insert into DeptB values(10,'Sales')

GO

insert into DeptB values(20,'Service')

GO

insert into DeptB values(30,'Marketing')

GO

--Intersect

select * From DeptA

Intersect

Select * From DeptB

GO




--Except

select * From DeptA

Except

Select * From DeptB

GO






Step by Step procedure of CLR Integration in SQL Server 2005


CLR integration in SQL Server 2005:


In T-SQL impossible when we need a stored procedure with some array-operations, text-parsing or complex math operations. However, it is quite easy if our Server allows us to deploy C# or VB.NET code that is used within the Server process. In other words, if we need complex procedural code, we can write it as managed code.


SQL Server 2005 we can write store procedure, triggers,user defined functions , user defined types etc in managed code(CLR) also. It acts as the operating system for the CLR when it is hosted inside SQL Server.


Procedure for Creating CLR integrated Store Procedure in SQL Server 2005 using Visual Studio 2005:

Open Visual Studio 2005 IDE.
· Go to menu File -> New -> Project.
· Within the “New Project” dialog box, select “database” (VB) as the project type and select “SQL Server Project” as the template. Provide your own project name, choose your own location and finally click OK.




You will be prompted with the “New Database Reference” dialog, where you need to provide the instance name of your SQL Server 2005 with database information.



When you are prompted for “SQL/CLR debugging” press “YES”.


Go to the Solution Explorer, right click on project and go to Add -> Stored Procedures




Select “Stored procedure” as in the template, provide the name of the stored procedure as “StoreProcedure1” and finally click add.





Add your code like this


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures1

Public Shared Sub SP_DisplayEmp()
Dim Con As New SqlConnection("context connection=true")
Try
Con .Open()
Dim Cmd As New SqlCommand("select eid,ename,sal from emp", Con)
Dim oReader As SqlDataReader
oReader = Cmd .ExecuteReader()
SqlContext.Pipe.Send(oReader)
Catch ex As Exception
SqlContext.Pipe.Send(ex.ToString())
End Try
End Sub

End Class


Here SqlContext.Pipe statement is an sql ouput statement.
After this ReBuild and Deploy the project through solution explorer.

Now new procedure named "SP_DisplayEmp" is added in master database. This procedure retrieve all the data which available in Emp table.


Open SQL server 2005 Query wizard and execute the following statements


use master
GO
--To enable SQL Server 2005 for CLR integration
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
create table emp
(
eid varchar(10),
ename varchar(40),
sal int
)
GO

insert into emp values('E01','Crys',81000)
GO
insert into emp values('E02','Jhon',72000)
GO
insert into emp values('E03','Gogul',90000)
GO

execute SP_DisplayEmp
GO


CLR integrated Procedure Ouput is

XML Serialization in .NET


XML Serialization:

By using XML serialization only public properties and fields can be serialized.

If private members are to be serialized, other serialization methods should be used (Binary Serialization and SOAP Serialization).

It requires a parameterless constructor. This constructor is called during deserialization.


Serialized form of the object does not contain any type, assembly information of the class. Only the data is stored.

Using XML serialization not all the classes can be serialized. Classes that implement IDictionary cannot be serialized. E.g. Hashtables cannot be serialized. Arrays of objects can be easily serialized. Collections can be serialized but to serialize collections certain rules must be followed.

Null values are not serialized with XML serialization. To include the null members in the serialized form IsNullable attribute should be set to true.

For example: [ XmlElement( IsNullable = true ) ]


Here ClsSample is a Serializable class. It has private and Public members,

using System;
using System.Collections.Generic;
using System.Text;

namespace XMLSerilization
{
public class ClsSample
{
private int m_TotalCount = 5;
public String m_StrWord = "Ten";

public void ReAssignMemberValues()
{
m_TotalCount = 20;
m_StrWord = "Twenty";
}
public void DisplayMemberValueAfterDeSerialization()
{
Console.WriteLine("Private member value is " + m_TotalCount);
Console.WriteLine("Public member value is " + m_StrWord);
}
}
}

Following way you can Serialize and Deserialize the objects(through files)

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace XMLSerilization
{
public class ClsXML
{
public void ToXMLSerilization(Object objToXml, string filePath)
{
StreamWriter stWriter = null;
XmlSerializer xmlSerializer;
try
{
xmlSerializer = new XmlSerializer(objToXml.GetType());
stWriter = new StreamWriter(filePath);
xmlSerializer.Serialize(stWriter, objToXml);
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (stWriter != null) stWriter.Close();
}
}

public object FromXMLSerilization(string filePath, Type type)
{
XmlSerializer xmlSerializer;
FileStream fileStream = null;
try
{
xmlSerializer = new XmlSerializer(type);
fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
object objectFromXml = xmlSerializer.Deserialize(fileStream);
return objectFromXml;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
if (fileStream != null) fileStream.Close();
}
}
}
}

//Main Class

using System;
using System.Collections.Generic;
using System.Text;

namespace XMLSerilization
{
public class Program
{
static void Main(string[] args)
{
ClsSample ObjSample;
ClsXML ObjXML;
ObjSample = new ClsSample();
ObjSample.ReAssignMemberValues();
ObjXML = new ClsXML();
//Serialize
ObjXML.ToXMLSerilization(ObjSample, "c:\\XMLSerialized.TXT");
ObjSample = null;
//Deserilize
ClsSample ObjDeseialXML;
ObjDeseialXML = (ClsSample) ObjXML.FromXMLSerilization("c:\\XMLSerialized.TXT", typeof(ClsSample)) ;
ObjDeseialXML.DisplayMemberValueAfterDeSerialization();
}
}
}

Output :
Private member value is 5
Public member value is Twenty

Actually reassigned value for the private member is 20 but you are unable to get that value in deserialized object. Because XML Serilization does not keep private members information

SOAP Serialization in .NET

SOAP Serialization :

SOAP Serialization and Binary Serialization having many similar properties.

In SOAP Serialization private fields, type information including assembly, namespace, public key token information are also serialized.

Deserialization gives the same object as it was before serialization.

Serialization can be controlled to exclude a field using [NonSerialized] attribute.

[Serializable] attribute is used to mark a class as serializable.

Classes do not need to have a parameterless constructor. Because of performance issues, the constructor of the class is not called during deserialization.

Use if the serialization and deserialization platforms are not .NET.

Use if the message is sent through a firewall.

Here ClsSample is a Serializable class. It has private and Public members,


using System;
using System.Collections.Generic;
using System.Text;

namespace SOAPSerilization
{
[Serializable]
public class ClsSample
{
private int m_TotalCount = 5;
public String m_StrWord = "Ten";
public void ReAssignMemberValues()
{
m_TotalCount = 20;
m_StrWord = "Twenty";
}
public void DisplayMemberValueAfterDeSerialization()
{
Console.WriteLine("Private member value is " + m_TotalCount);
Console.WriteLine("Public member value is " + m_StrWord);
}
}
}

Following way you can Serialize and Deserialize the objects(through files)

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Soap;

namespace SOAPSerialization
{
public class ClsSOAP
{
//Serialization
public void ToSoapSerialization(Object objToSoap,string filePath)
{
IFormatter formatter;
FileStream fileStream = null;
try
{
fileStream = new FileStream(filePath,FileMode.Create, FileAccess.Write);
formatter = new SoapFormatter();
formatter.Serialize(fileStream, objToSoap);
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (fileStream != null) fileStream.Close();
}
}

//Deserialization
public object FromSoapSerialization(string filePath)
{
IFormatter formatter;
FileStream fileStream = null;
try
{
fileStream = new FileStream(filePath,FileMode.Open, FileAccess.Read);
formatter = new SoapFormatter();
return formatter.Deserialize(fileStream);
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (fileStream != null) fileStream.Close();
}
}
}
}

//Main Class
using System;
using System.Collections.Generic;
using System.Text;

namespace SOAPSerialization
{
public class Program
{
static void Main(string[] args)
{
ClsSample ObjSample;
ClsSOAP ObjSOAP;
ObjSample = new ClsSample();
ObjSample.ReAssignMemberValues();
ObjSOAP = new ClsSOAP();
//Serialize
ObjSOAP.ToSoapSerialization(ObjSample, "c:\\SOAPSerialized.TXT");
ObjSample = null;
//Deserilize
ClsSample ObjDeseialSOAP;
ObjDeseialSOAP = (ClsSample)ObjSOAP.FromSoapSerialization("c:\\SOAPSerialized.TXT");
ObjDeseialSOAP.DisplayMemberValueAfterDeSerialization();
}
}
}

Output:

Private member value is 20

Public member value is Twenty

Here both private and public members are serialized


ROLLUP in Sql Server

it is used to Summarizing Data..

The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator.

ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Checkout this example..

Create Table Student
(
StudentID int,
Student_Name Varchar(15),
SubjectName Varchar(15),
Marks_Scored numeric(18,1)
)

Insert into Student Values(1,'Babu', 'Tamil',90)
Insert into Student Values(1,'Babu', 'English',78)
Insert into Student Values(1,'Babu', 'Maths',88)
Insert into Student Values(1,'Babu', 'Science',88)
Insert into Student Values(1,'Babu', 'Social Science',92)

Insert into Student Values(2,'Ramesh', 'Tamil',92)
Insert into Student Values(2,'Ramesh', 'English',88)
Insert into Student Values(2,'Ramesh', 'Maths',98)
Insert into Student Values(2,'Ramesh', 'Science',99)
Insert into Student Values(2,'Ramesh', 'Social Science',90)

Insert into Student Values(3,'ram', 'Tamil',65)
Insert into Student Values(3,'Ram', 'English',68)
Insert into Student Values(3,'Ram', 'Maths',88)
Insert into Student Values(3,'Ram', 'Science',59)
Insert into Student Values(3,'Ram', 'Social Science',60)

select Student_Name,Sum(Marks_Scored) as Total
from Student
group by Student_Name
with rollup

Select
(Case When (Grouping(Student_Name)=1) Then 'G.Total' Else Student_Name End)as Student_Name,
(Case When (Grouping(SubjectName)=1) Then 'Total' Else SubjectName End)as SubjectName,
Sum(Marks_Scored) as Total
from Student group by Student_Name, SubjectName with rollup






Note :it will run sqlserver2005 and above

Serial Number using ROW_NUMBER (Transact-SQL)

Serial No is possible for your table using ROW_NUMBER


ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Check out this Example


create table myTable
(
intNo int,
fname varchar(200),
mark numeric(5,2)
)


insert into mytable values(1,'xyz',89.45)
insert into mytable values(2,'xyz1',93.45)
insert into mytable values(3,'xyz2',35.45)
insert into mytable values(4,'xyz3',88.45)
insert into mytable values(5,'xyz4',73.45)



select ROW_NUMBER()OVER(ORDER BY mark DESC) AS 'Row Number' ,intNo,
fname,mark from MyTable

output



Note:it will run in sql server 2005 and above...

Difference between MSDE and SQL Server Express 2005


SQL SERVER Express edition is a cut down version of SQL SERVER 2005 and the next evolution of MSDE(MSDE is a cut down version of SQL Server 2000).


  • MSDE maximum database size is 2GB but in SQL SERVER Express has around 4GB.

  • In terms of programming language support MSDE has only TSQL, but SQLSERVER Express has TSQL and .NET.

  • SQL SERVER Express does not have connection limitation, which MSDE had and was controlled through the Workload governer.

  • There was no XCOPY support for MSDE, SQL SERVER Express has it.

  • DTS is not present in SQL SERVER express while MSDE has it.

  • SQL SERVER Express has reporting services while MSDE does not.

  • SQL SERVER Express has native XML support and MSDE does not.

Database restore from SQL Server 7 ,SQL Server 2000 to SQL Server 2005

it is possible to restore database from sql server 7, sql server 2000 to sql server 2005.
it can be achieved by using restore and with replace command

use master

restore database mydatabase from disk ='c:\mybackup.bak' with replace

Sql Server – Backup and Restore Database using SQL Script (T-SQL)

it is easy to take backup in sql server using script without any External tools.
By SIMPLE ways.. as follows...

use master

create database mydatabase

backup database mydatabase to disk ='c:\mybackup.bak'

Yeah,i can be easily restore by simple steps...

use master

restore database mydatabase from disk ='c:\mybackup.bak'

thats all..

Parameter types in C# (Value,out,ref and params)


Value parameters:

Value parameter is also called In parameter. A parameter declared with no modifiers is a
value parameter. Methods are permitted to assign new values to a value parameter. Such assignments only affect the local storage location and it has no effect on the actual argument given in the method invocation.

static void ParamMethod(int Param1)
{
Param1 = 100;
}
static void Main()
{
int Paravalue = 10;
ParamMethod(Paravalue);
Console.WriteLine(Paravalue);
}

//Here the output is 10

Reference Parameter:

Reference parameter declared with a ref modifier. It does not make a new storage location. Instead, a reference parameter represents the same storage location as the variable given as the argument in the method invocation. It should assigned to some value before it go for the method invocation.

static void ParamMethod(ref int Param1)
{
Param1 = 100;
}
static void Main()
{
int Paravalue = 10; //Here Paravalue should assign
ParamMethod(ref Paravalue);
Console.WriteLine(Paravalue);
}

//Here the output is 100

Output parameters:

Output parameter declared with an out. Similar to a reference parameter, an output parameter does not create a new storage location. Instead, an output parameter represents the same storage location as the variable given as the argument in the method invocation.Every output parameter of a method must be definitely assigned before the method returns.

static void ParamMethod(out int Param1)
{
Param1 = 100; //Here Param1 should assign
}
static void Main()
{
int Paravalue ;
ParamMethod(out Paravalue);
Console.WriteLine(Paravalue);
}

//Here the output is 100

Params:

The value passed for a "params" parameter can be either comma separated value list or a single dimensional array. "params" parameters are input only.

static int Summation(params int[] Param1)
{
int Sum =0;
foreach(int P in Param1)
{
Sum = Sum +P;
}
return Sum ;
}

static void Main()
{
Console.WriteLine(Summation(1,2,3));
}

//Here the output is 6

Serailization in .NET


Serialization:

Serialization is the process of saving the state of an object in a persistent storage media in the form of linear stream of bytes. The object can be stored to a file, a database or even in the memory. Serialization in .NET is provided by the System.Runtime.Serialization namespace.

In order for a class to be serializable, it must have the attribute SerializableAttribute set
and all its members must also be serializable, except if they are ignored with the attribute NonSerializedAttribute. However, the private and public members of a class are always serialized by default. The SerializationAttribute is only used for the binary serialization.

[Serializable]
public class Student
{
private int StudentID;
public string StudentName;
public void dispaly()
{
Console.WriteLine("Student Name "+ StudentName);
}
}


Binary Serialization:

In Binary serialization the entire object state is saved. Both public and private members states are saved.

//The following way Student object is serialized
public void BinarySerialize(string filename, Student Stud)
{
FileStream fileStreamObject;
try
{
fileStreamObject =
new FileStream(filename, FileMode.Create);
BinaryFormatter binaryFormatter = new BinaryFormatter();
binaryFormatter.Serialize(fileStreamObject, Stud);
}
finally
{
fileStreamObject.Close();
}
}

//The following way Deserialize the Student Object
public static object BinaryDeserialize(string filename)
{
FileStream fileStreamObject;

try
{
fileStreamObject = new FileStream(filename, FileMode.Open);
BinaryFormatter binaryFormatter = new BinaryFormatter();
return (binaryFormatter.Deserialize(fileStreamObject));
}
finally
{
fileStreamObject.Close();
}
}


Serialization is used to transportation of an object through a network.

How to Fix cross mark in Crystal report while exporting to Excel

In your application, while exporting crystal report to Excel your getting cross mark in spite of image.Like this






Problem is not in the crystal report.Dont waste time your in Crystal report.

Solution is in your Business logic,check your size of the image/bitmap.

Ensure image height and width are same before exporting to crystal report.

it is working for me..Enjoy it..










How to Fix/Solution for Internet Explorer cannot open the Internet site and operation aborted in Blog

In blog, your getting message like this Internet Explorer cannot open the internet site with following links and operation aborted...


Check out your Blog spot settings, where your Comment form placement in your blog. If it is Embedded below post like this...

Solution is simply and Easy,Change it Embedded below post to Full page.

it is working...


ahhahhha...Enjoy it...

How to Call Javascript function through CodeBehind(C#)

Yeah,It is easy to Call javascript function through codebehind by using
Attributes.Add to the button.

use this code..

Button1.Attributes.Add("onclick", "return callingfn()");

Consider javascript as follows

<script defer="defer" type="text/javascript" language="javascript">

function callingfn()

{

var objTextBox = document.getElementById('TextBox2');

objTextBox.value = "123";

return objTextBox.value();

}

</script>


whole code like this..

<html >

<head runat="server">

<title> Untitled Page</title>

<script defer="defer" type="text/javascript" language="javascript">
function
callingfn()

{

var objTextBox = document.getElementById('TextBox2');

objTextBox.value = "123"

return objTextBox.value();

}

</script>

</head>

<body>

<form id="form1" runat="server">

<div>

<table>

<tr>

<td>

<asp:TextBox ID="TextBox2" runat="server" > </asp:TextBox> </td>

</tr>

<tr>

<td align ="center"> <asp:Button ID="Button1" runat="server" Text="Button" />

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

SQL Server 2000 Workload Governer


Workload governor limits the performance of MSDE if the SQL engine receives more load than limit of MSDE(8 Cuncurrent connection).

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.

It does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE.

Difference between MSDE and SQL Server 2000

MSDE(Microsoft Desktop Engine):

MSDE is a cut down version of the SQL SERVER database and free, redistributable software.

Differences:

A.Size:

In MSDE database size limited to 2GB.
In SQL Server database size limited to 1,048,516 TB1(1 Billion GB).

B.Performance:

Performance of MSDE degrades when maximum number of concurrent operations greater then or equal to 8. Performance degradation is implemented with the help of SQL SERVER 2000 workload governor.

In SQL SERVER 2000, possible number of concurrent connections are 32,767.

C.OLAP and Data warehousing:

MSDE does not provide OLAP and Data warehousing capabilities.

D.Mail:

MSDE does not have support facility for SQL mail.

E.Administrative Tools:

MSDE doesn't have its own administrative tools. There are many third party tools, which provide administrative capability GUI.

SQL Server have administrative tools such as enterprise manager, Query analyzer or Profiler.

Ref : www.codeproject.com/

How to Filter Window Message in .NET

Message Filter:

Message filter to prevent specific events from being raised to the control or Forms.

We can achieve the Message Filter through IMessageFilter Interface. It has overridable method name PreMessageFilter.Using this method we can customize our Message Filters.

Example : Filter/Restrict left mouse button down message

public class MyMsgFilter : IMessageFilter
{
public bool PreFilterMessage(ref Message m)
{
// Filter/Restrict left mouse button down message.
if (m.Msg == 513)
{
return true;
}
return false;
}
}

In order to monitor the Message Filter in our application you must register Filter class in your application.

public class mainForm : System.Windows.Forms.Form
{
private MyMsgFilter msgFilter = new MyMsgFilter();
public mainForm()
{
// Add message filter.
Application.AddMessageFilter(msgFilter);
}
}

Following way you can remove your Message Filter
Application.RemoveMessageFilter(msgFilter);