Static Class

Static classes are used when a class provides functionality that is not specific to any unique instance. Here are the features of static classes in C# 2.0.

•Static classes can not be instantiated.
•Static classes are sealed so they can not be inherited.
•Only static members are allowed.
•Static classes can only have static constructor to initialize static members.

Advantages :
Compiler makes sure that no instance of static class is created. In previous version of C#, the constructor has to be marked private to avoid this from happening.

Also compiler makes sure that no instance members are declared within a static class.

One of the biggest problems we VB developers migrating to C# face is understanding STATIC. Static constructors, static data, static member functions, static properties.

To a VB developer, “static” means a variable inside a function (method) whose value remains “live” between function calls, but which is invisible outside the function. In C#, there is no such concept, because it’s not necessary when you can define member fields instead.

What is the purpose of connection pooling in ado.net

Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a connection pool, an application can reuse that connection without performing the complete connection creation process.

By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released. But be sure than your connections use the same connection string each time. Here is the Syntax

conn.ConnectionString = “integrated Security=SSPI; SERVER=192.168.0.123; DATABASE=MY_DB; Min Pool Size=4;Max Pool Size=40;Connect Timeout=14;”;

What is Strong Name/Key

In Microsoft.Net a strong name consists of the assembly’s identity. The strong name guarantees the integrity of the assembly. Strong Name includes the name of the .net assembly, version number, culture identity, and a public key token. It is generated from an assembly file using the corresponding private key. Steps to create strong named assembly: To create a strong named assembly you need to have a key pair (public key and a private key) file.

Use sn -k KeyFile.snk Open the dot net project to be complied as a strong named assembly. Open AssembyInfo.cs/ AssembyInfo.vb file. Add the following lines to AssemblyInfo file. [Assembly: AssemblyVersion("1.0.*")] [assembly: AssemblyDelaySign(false)] [assembly: AssemblyKeyFile("..\\..\\KeyFile.snk")] Compile the application, we have created a strong named assembly.

Asp.net Reserved/ App folder

This article is about the special "reserved" folders that are used in an ASP.NET project, such as App_Code or App_Data. Each one is discussed in details, with suggested uses for it.

Note that your ASP.NET project may or may not contain these directories. They are optional, and the uses given by Microsoft are only suggested uses. You are free to use them or not use them, for any purpose you like.

If you want to add one of these folders, go to Solution Explorer and right-click on your project's name at the top of the list. Select "Add ASP.NET folder" and pick the one(s) you want to add.

App_Data can be used to store datafiles such as databases that are used by this application. One common use is as a location to store the ASPNETDB.MDF file that is used to store usernames and passwords for authenticated website access.

App_Code is typically used to store .cs or .vb files that contain classes that are needed by your main code. Any file you put in here is automatically referenced in your main application, which is very handy. I put libraries of useful functions wrapped up in a static class here, and the compiler automatically picks them up and uses them.

App_Browsers stores special config files for individual browsers' settings. I've never needed to use this, but if you are writing code to target many unusual browsers, perhaps you could find a use for it.

App_GlobalResources is used to store .resx and .resources files, such as files for translation of your website into different languages. These files apply to the entire project.

App_LocalResources is a reserved folder name that is like App_GlobalResources, except that you can have more than one of them, in any folder in your application. It acts like a local version of App_GlobalResources that only applies to that particular folder.

App_Themes is a folder used to store any files that change the appearance of the website, such as .css files, .skin files, or images that are used in a certain theme.

Bin is the folder to put .dll files in if you want your whole application to have access to the dll file. Anything you put here, such as a third-part control, is available to your whole application.

App_WebReferences is a mystery folder that I don't understand yet!

Difference Between binary_checksum and checksum

binary_checksum is used to compare password only but checksum is used to compare all cases characters.

select binary_checksum('a')
select binary_checksum('A')
select checksum('a')
select checksum('A')

Result
97
65
142
142

Search some text from Stored Procedure

If we want to search the stored procedure name for the text then we can try

Select Distinct SO.Name from sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id=SC.ID and SO.Type = 'P' AND SC.Text LIKE '%%distinct%city from dmslocation%' order by SO.Name

Automating Birthday Email

1. You can schedule a job at 05:00 AM every day to accompish this, just open Enterprise Manager, explorer MySQLInstance->'Management'->'SQL Server Agent'->right click 'Jobs'->'New Job', then go to 'Steps' pannel to new a step. Choose 'T-SQL' as step type, and copy your T-SQL script used for sending email to the 'Command' field (as you'll schedule the script to run once a day, let's remove the 'WHILE(1=1)' loop). Then go to 'Schedules' pannel to schedule your job step. You can press F1 for detailed help. Sorry I wrote a wrong script in my previous post, for the case that there may be more than 2 persons have same birhday:

declare @email varchar(100), @name varchar(100)

select email,name into #tbl
from tbl_Birthday
where month(birthdate)=month(getdate())
and day(birthdate)=day(getdate())
select @eamil=email,@name='Happy Birthday'+name+'!' from #tbl
WHILE (@email is not null)
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = @email,
@subject = N'Happy Birthday!',
@message = @name,
@attachments='c:\happyBirth.html'--you have use attachment if you want HTML feature
DELETE FROM #tbl where email=@email
select @eamil=email,@name='Happy Birthday'+name+'!' from #tbl
END
DROP TABLE #tbl
------------------------------------
Suggested site:
http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

Disabling Items Of Drop Down List

ListItem i = DropDownList1.Items.FindByValue("Finished");
i.Attributes.Add("style", "color:gray;");
i.Attributes.Add("disabled", "true");
i.Value = "-1";

Show Processing on Page using Modal Popup

Write the following code inside form tag and updatepanel.

< script type="text/javascript" language="javascript" >
// register for our events
Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(beginRequest);
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(endRequest);

function beginRequest(sender, args)
{
// show the popup
$find('mdlPopup').show();
// if we are using this fanda in master page then
// $find(' < %=mdlPopup.ClientID% >').show();
}

function endRequest(sender, args)
{
// hide the popup
$find('mdlPopup').hide();
//WindowScroll();
// if we are using this fanda in master page then
// $find(' < %=mdlPopup.ClientID% >').show();
}
< /script >

//////////////////////////

< cc1:ModalPopupExtender ID="mdlPopup" runat="server" TargetControlID="pnlPopup"
PopupControlID="pnlPopup" BackgroundCssClass="modalBackground" >
< /cc1:ModalPopupExtender >

< asp:Panel ID="pnlPopup" runat="server" CssClass="updateProgress" style="display:none" >
< div align="center" class="ProgressBarCenter" >
< asp:Image ID="img" runat="server" ImageUrl="~/Images/simple.gif" / >
< span class="updateProgressMessage" > Loading ... < /span > < /div >
< /asp:Panel >

Partial Page Print in HTML

Write this function in javascript tag and call in your page. Here "printingstuff" is a div tag.

function printDiv()
{
var printer = window.open('','','width=640,height=700');
printer.document.open("text/html");
printer.document.write(document.getElementById('printingstuff').innerHTML);
printer.document.close();
printer.window.close();
printer.print();
alert("Printing the \"printingstuff\" div...");
}

Speed up the Queries, Reindex the tables

Whenever we get timeout error, we need To Speed up the queries by running the following Queries in Database
1) First Way is
ALTER INDEX ALL ON Tablename REBUILD WITH (FILLFACTOR = 80)
UPDATE STATISTICS Tablename WITH FULLSCAN, NORECOMPUTE
EXEC SP_UPDATESTATS

2) Second Way is
DBCC DBREINDEX (Tablename, '', 90)

SQL Data Caching

using System.Web.Caching;
using System.Data.SqlClient;

public partial class MyCaching : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds;
ds = (DataSet)Cache["firmCustomers"];
if (ds == null)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GetitDMConnectionString2"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("Select top 20 areaname, pincode from area", conn);
ds = new DataSet();
da.Fill(ds);
SqlCacheDependency myDependency = new SqlCacheDependency("DMS", "area");
Cache.Insert("firmCustomers", ds, myDependency);
Label1.Text = "Produced From database";
}
else
{
Label1.Text = "Produced From Cache";
}
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
============================
Enabling the Database and Table

d:\program files\microsoft visual studio 9.0\VC>

Run these commands in Command promt On above prompt

aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 -d DMS -ed
aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 - d DMS -t Userlogin -et
====================================

//webconfig
Symbols
LL => Less Than
GG => Greater Than

< connectionStrings >
< add name="GetitDMConnectionString2" connectionString="Data Source=192.168.0.181;Initial Catalog=DMS;Persist Security Info=True;User
ID=sa;Password=sa123;Connect Timeout=900; pooling='true';Max Pool Size=200" providerName="System.Data.SqlClient"/ >
< /connectionStrings >

< system.web >
< caching >
< sqlCacheDependency enabled="true" >
< databases >
< add name="DMS" connectionStringName="GetitDMConnectionString2" pollTime="500" / >
< /databases >
< /sqlCacheDependency >
< /caching >

using System.Web.Caching;
using System.Data.SqlClient;

public partial class MyCaching : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds;
ds = (DataSet)Cache["firmCustomers"];
if (ds == null)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GetitDMConnectionString2"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("Select top 20 areaname, pincode from area", conn);
ds = new DataSet();
da.Fill(ds);
SqlCacheDependency myDependency = new SqlCacheDependency("DMS", "area");
Cache.Insert("firmCustomers", ds, myDependency);
Label1.Text = "Produced From database";
}
else
{
Label1.Text = "Produced From Cache";
}
GridView1.DataSource = ds;
GridView1.DataBind();
}
}

=======================
Enabling the Database and Table

d:\program files\microsoft visual studio 9.0\VC>
Run these commands in Command promt On above prompt
aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 -d DMS -ed
aspnet_regsql.exe -S "192.168.0.181" -U sa -P sa123 - d DMS -t Userlogin -et

========================
//webconfig
Symbols:
LL Means Less Than
GG Means Greater Than

Use of Case when Statement with multiple condition

SELECT distinct city,
(CASE city
WHEN 'Delhi' THEN '11'
WHEN 'chennai' THEN '22'
ELSE '33'
END) as STDCode
from area

Select All Checkbox on Click of One checkbox in Javascript

==Script in Javascript===
function SelectAllCheckboxes(spanChk)
{
// Added as ASPX uses SPAN for checkbox
var oItem = spanChk.children;
var theBox= (spanChk.type=="checkbox") ?
spanChk : spanChk.children.item[0];
xState=theBox.checked;
elm=theBox.form.elements;

for(i=0;iif(elm[i].type=="checkbox" && elm[i].id!=theBox.id)
{
if(elm[i].checked!=xState)
elm[i].click();
}
}

==Check all checkbox on selection of A Checkbox
input id="Checkbox2" type="checkbox" onclick="javascript:SelectAllCheckboxes(this);" / Check All

Clear the Controls Data on Click of Reset Button

I have 20 controls on my web page. I want to clear the controls data when onclick of reset button:
private void ClearControls(Control parent)
{
foreach (Control _ChildControl in parent.Controls)
{
if ((_ChildControl.Controls.Count > 0))
{
ClearControls(_ChildControl);
}
else
{
if (_ChildControl is TextBox)
{
((TextBox)_ChildControl).Text = string.Empty;
}
else
if (_ChildControl is CheckBox)
{
((CheckBox)_ChildControl).Checked = false;
}
else
if (_ChildControl is DropDownList)
{
((DropDownList)_ChildControl).SelectedIndex = 0;
}
else
if (_ChildControl is ListBox)
{
((ListBox)_ChildControl).SelectedIndex = 0;
}
}
}
}


calling this function like this:pn click of reset button
ClearControls(this.Page);

C# Conditions in 2 different ways

Checking condition in 2 different way

One Way is
if (item.Text.ToString() == "Professional")
query1 = "P";
else
query1 = "C";

--------
query1 = item.Text.ToString() == "Professional" ? "P" : "C";

================Separate
if (item.Selected == true)
query1 = item.Text.ToString() == "Professional" ? "P" : "C";
---------
if (item.Selected != true) continue;
query1 = item.Text.ToString() == "Professional" ? "P" : "C";

Referencing Blogs

http://7explications.wordpress.com/
http://sajithpremachandran.blogspot.com/
http://sajithpremachandran.wordpress.com/
http://sairamjvh.blogspot.com/
http://arrao4u.wordpress.com/
http://aspdotnetcodebook.blogspot.com/

Complete basic Tutorial of WCF

http://www.wcftutorial.net/

Concatenate Row Data in Column

Existing Table:- Surrounding_City

Id City SurroundingCity
67 Madurai Madurai
68 Madurai Dindigul
69 Madurai Palani
70 Madurai Kodaikanal
71 Madurai Sivakasi
72 Madurai Virudhunagar
73 Madurai Rajapalayam
74 Madurai Theni


Step 1:: Create Function
Create Function CityConcatenate (@City Varchar(100)) returns varchar(2000)
as
begin
Declare @var varchar(2000)
Set @var='';
Select @var=@var+'

'+surroundingcity+'

' from surrounding_city Where city=@City
return @var
end

Step 1:: Call Function in Query
Select distinct city,dbo.CityConcatenate(city) from surrounding_city

Looping in Cities to insert

Alter proc TestCity_Assigned --'bihar'
(@state varchar(100))
as begin
declare @mycity varchar(100)

declare cursor1 cursor for
select distinct city from area (nolock) where state=@state
open cursor1
fetch next from cursor1 into @mycity

while @@fetch_status=0
begin
if not exists(select * from city_assigned (nolock) where name='Bikash' and assigned_city=@mycity)
insert into city_assigned(name,assigned_city,status) values('Bikash',@mycity,'Super Manager')
fetch next from cursor1 into @mycity
end
close cursor1
deallocate cursor1
end

TestCity_Assigned 'bihar'

Looping In Sql Server

DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
END
IF(@Flag > 5)
BREAK
ELSE
CONTINUE
END

Function to Concatenate Strings In SQL

Alter function dbo.ConcatAreaName(@pincode varchar(20))
RETURNS varchar(2000)
As
Begin
Declare @var varchar(2000)
set @var=''
Select @var = @var +'#'+ areaname from area where pincode=@pincode group by areaname
return @var
End

To Get the Result from Function :
Concatenate Areaname on the basis of pincode :
select distinct pincode, dbo.ConcatAreaName(pincode) from area where pincode='124505'
select distinct top 10 pincode, dbo.ConcatAreaName(pincode) from area

Capturing IP Address and System Name

Response.Write("Host name : "+ System.Net.Dns.GetHostName().ToString());

Response.Write("Host address : "+System.Net.Dns.GetHostAddresses(System.Net.Dns.GetHostName()).GetValue(0).ToString());

Generate a Random Number

Run this qry in SQL :

declare @random varchar(50)
set @random = newid()
print (@random)
select substring(@random,1, 16)

Difference Between DDL, DML, DCL and TCL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency


Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Clear the Controls Data on Click of Reset Button

I have 20 controls on my web page. I want to clear the controls data when onclick of reset button:

private void ClearControls(Control parent)
{
foreach (Control _ChildControl in parent.Controls)
{
if ((_ChildControl.Controls.Count > 0))
{
ClearControls(_ChildControl);
}
else
{
if (_ChildControl is TextBox)
{
((TextBox)_ChildControl).Text = string.Empty;
}
else
if (_ChildControl is CheckBox)
{
((CheckBox)_ChildControl).Checked = false;
}
else
if (_ChildControl is DropDownList)
{
((DropDownList)_ChildControl).SelectedIndex = 0;
}
else
if (_ChildControl is ListBox)
{
((ListBox)_ChildControl).SelectedIndex = 0;
}
}
}
}


calling this function like click of reset button ClearControls(this.Page);

Change Color Of Gridview On Mouse-Over

protected void gridRole_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes["onmouseover"] = "this.style.cursor='hand';this.style.textDecoration='underline';this.style.background='#E2DED6';";

e.Row.Attributes["onmouseout"] = "this.style.textDecoration='none';this.style.background='none';";
}
}

Gridview Selected index change

protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
int i = Convert.ToInt32(e.NewSelectedIndex);
GridViewRow row = GridView1.Rows[e.NewSelectedIndex];
Label LocationId = (Label)row.FindControl("LblLocationID");
Label MasterId = (Label)row.FindControl("LblMasterID");
Response.Write("script window.opener.parent.location='locationeditnew.aspx?location_id=" + Server.UrlEncode(LocationId.Text) + "&masterid=" + Server.UrlEncode(MasterId.Text) + "';this.close();/script");
}

Show Gridview Row in Red Color on the basis of Inner Value

Write following code in Design:

asp:TemplateField HeaderText="Status"
ItemTemplate
asp:Label ID="Lblstatus" runat="server" Text='%# Eval("status") %'/asp:Label
/ItemTemplate
/asp:TemplateField

Write following code in .cs file:
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lbl = (Label)e.Row.FindControl("Lblstatus");
if ((string)DataBinder.Eval(e.Row.DataItem, "status").ToString().ToUpper() == "D")
{
e.Row.BackColor = System.Drawing.Color.Red;
e.Row.Font.Bold = true;
}
}
}

Unlock the table in Sql Server 2005

By using SP_Lock you get the info on the locks, I means locks on the objects like Tables. You can also find the SPID (Processor Id) which is locking this.
By using Kill command you can kill the processor which intern releases the locks.
Following is the example:
EXEC sp_lock --> all the objects with the lock info and find the processor locking the required object let us ay spid is 54
or

dbcc opentran

dbcc inputbuffer(yourprocessid)

KILL 54 --> this will kill the processor id 54.
Please make sure that you are killing only the processor which is locking your objects.

Difference between Stored Procedure and Function

1. You cannot use stored procedure in a select statement likeselect but this is possible with UDF .
2. You can not return a table type variable with stored procedure but this is possible with user defined functions in sql Server.
3. Stored procedure may or may not return a value but a function has to return a value.
4. UDFs can accept a smaller number of parameters than stored procedures. UDFs can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.
5. UDFs cannot call stored procedures (except extended procedures), whereas stored procedures can call other procedures.

Difference Between @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers.

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%'