Cannot ALTER TABLE in MS SQL 2008/SQL2008 R2/SQL2012


After installing ms sql 2008/2012 you may face new problem which may protect you to alter any table. Something like following
Alter Table Problem In MS SQL

There is a new option added in SQL Server 2008/2012 for modifying tables while using the Table Designed in Management Studio which will prevent saving any changes to a table that needs a drop/re-create of the table.
The error you see

Continue reading

Selecting Record from XML Valued Column in MS SQL

Just Consider we have following records in a object named CR_NonDPC_ConfigDTO

PCName : PC
MotherBoard : ASROK 945 GCM
Processor : Pantium
HDD : HDD 160 GB
RAM : DDR-2 1 GB
Cassing : NULL
ExtraConfig : CD ROM ASUS

Now we shall convert it to XML (for converting Object to XML you can check here)
Here is the xml of above object
Continue reading

Executing *.sql file form C#

Sometimes we need to run *.sql script from our application for installing data base. SqlCommand is not right for running installation script because installation script consists of DDL and GO command. Here we use smo library for executing *.sql script.

For doing above operation we need to add following references

Continue reading

Sending Email using C# (with HTML body & Attachment )

This article is provide a basic idea how we can send Emails from a C# Windows Application.
Here I have described how we can send mail with Attachment and HTML body.

If we want to send mail then we need to know following things
1. SMTP Server address
2. Port No

Here I have used gmail as a SMTP Server
for gmail account
SMTP Server:
SMTP port : 465 or 587
your account Name:###
your password:###

Continue reading

Converting List to DataTable in C#

There is no built in method for converting list to data table in C#.
So we can implement following method for converting List to DataTable in C#.

 public DataTable ToDataTable(IList data)
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(t));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            object[] values = new object[props.Count];
            foreach (t item in data)
                for (int i = 0; i < values.Length; i++)
                    values[i] = props[i].GetValue(item);
            return table;

Filter data by date time in MS access, Select only date without selecting time from date time field in MS Access

Select * from TransactionInfo where CDate(Int([CreatedDate])) between #2010-05-04# and #2010-05-05#
it will return date like


Select * from TransactionInfo where [CreatedDate] between #2010-05-04# and #2010-05-05#

it will return

Continue reading


Here I shall describe about split in MS SQL.

There is no built in function for splitting your text, that’s why I am describing two customize function for performing split operation in MS SQL.

Split function 1: This function returns table that’s why we can say it table valued function.

--select * from fnSplit('1,22,333,444,,5555,666', ',')
  @sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

 Continue reading