Reduce Size of Database

If you the getting the error like your transaction log is full when updating then put this query
USE DatabaseName
GO
DBCC SHRINKdatabase(DatabaseName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKdatabase(DatabaseName, 1)

--dbcc opentran
--kill 56
--SELECT * FROM sys.dm_os_waiting_tasks;
--SELECT * FROM sys.dm_tran_locks

Transaction Log is full

If you the getting the error like your transaction log is full when updating then put this query
USE DatabaseName
GO

use these queries to remove the lock
--dbcc opentran
--kill 56
--SELECT * FROM sys.dm_os_waiting_tasks;
--SELECT * FROM sys.dm_tran_locks

DBCC SHRINKdatabase(DatabaseName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKdatabase(DatabaseName, 1)

Upload Large files using asp.net

To upload large files in asp.net you have to increase the MaxrequestLength and Executiontimeout of the httpruntime settings of your web.config file.By Default it will upload 4MB.
suppose if i want to upload upto 8 MB you have to include this tag in you web.config file , don't change anything in your machine.config files.
httpruntime maxRequestLength="1048576" executionTimeout="450"

here 1048576 - 1 GB

Avoid execution of code while refresh Page

Hi , here we have to discuss the how to avoid the execution of code While you refresh the page.. suppose if you have written the code for sending email to users.. after clicking the button the code has been executed suppose if you , Refresh the same page once again the Email sent to the user so here we have to avoid the execution on code on page_Refresh
This is code for C#.NET
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["CheckRefresh"] = Server.UrlDecode(System.DateTime.Now.ToString());
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (Session["CheckRefresh"].ToString() == ViewState["CheckRefresh"].ToString())
{
Label1.Text = "Hello";
Session["CheckRefresh"] = Server.UrlDecode(System.DateTime.Now.ToString());
}
else
{
Label1.Text = "Page Refreshed";
}
}
protected void Page_PreRender(object sender, EventArgs e)
{
ViewState["CheckRefresh"] = Session["CheckRefresh"];
}

for a city having multiple count

select x.city,(select count(*)from dmslocation ywhere y.city = x.city) as "Total Count",(select count(*)from dmslocation ywhere y.city = x.cityand y.validated = 'No') as "Count of No",(select count(*)from dmslocation ywhere y.city = x.cityand y.validated = 'Yes') as "Count of yes" from dmslocation xgroup by city

Select State of different Case

select distinct city ,count(distinct city COLLATE SQL_Latin1_General_CP1_CS_AS) from area group by city having count(distinct city COLLATE SQL_Latin1_General_CP1_CS_AS)>1

--To Update
update dmslocation set city='Thrissur' where city COLLATE SQL_Latin1_General_CP1_CS_AS not in('Thrissur') and city like '%Thrissur%'