Link to home
Start Free TrialLog in
Avatar of dirkmartin
dirkmartinFlag for United States of America

asked on

Is it possible to change case sensitivity, in SQL Server 2005?

It is my understanding that in sqlserver 2000, it was quite simple to change the collation, thus removing case sensitivity for variable comparisons within stored procs.

Is there an easy way to do this, in 2005?
Avatar of Raynard7


This is possible

please see for a tutorial about how it is done

Avatar of EugeneZ

Solution:  The COLLATE clause can make any string operation case sensitive.
The solution turned out to be pretty easy, use a COLLATE clause with a case sensitive collation.  Here's a sample query with both case senstive and case insensitve collations. The data is all in the example, so give them a try.

select distinct (Item) COLLATE sql_latin1_general_cp1_cs_as
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items

All that is different in the next query is the name of the collation:

select distinct (Item) COLLATE sql_latin1_general_cp1_ci_ai
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items

Avatar of dirkmartin


Raynard7, the article you reference states the following, on page 2:

Change the collation

In SQL Server 7.0, Collation can be setup only on the server level during installation. In order to change the collation, you have to rebuild the master. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

In SQL Sever 2000, you can change collation on the database level and column level just by using alter statements.

As you can see, the method of handling this in 7.0 is ugly.  In 2000, it's sweet.
I'm looking for some kind of "alter statement", for a 2005 database.


Are you suggesting that I need to change every single stored procedure, and add that Collate clause?  

There's no way to do this, database wide, like in version 2000 ?

ALTER DATABASE your_DB COLLATE sql_latin1_general_cp1_cs_as
I tried that alter statement, but keep getting:

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'Project_db' cannot be set to SQL_Latin1_General_CP1_CS_AS.

In my "Available Databases" drop down, I made sure I had "master" selected, rather than the data base I was trying to alter, to no avail.
<The database could not be exclusively locked to perform the operation.>
make sure noone access the database
exec sp_who

then run from master again
exec sp_who
if you see Spid with access to the database
you may run kill spid# (make sure you do not kill important connections)
I see 2 other Spid's that I can kill.
(other instances of myself)

kill spid 53    doesn't work
kill spid53     doesn't work

What's the syntax gang?
ok, i figured out the syntax.
It looks to me, that the "Alter Database....database....collation..."  does work with 2005.
So, I'm gonna assign the points to EugeneZ
EugeneZ, how can I test this?
What are the two collations to invoke case insensitivity and sensitivity?
And would would be a simple test, within a sproc?
Avatar of EugeneZ
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

create procedure usp_test
select getdate()


exec usp_test
exec Usp_test

sp_helpdb  yourdb
and see 'status' field

something like:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CS_AS, SQLSortOrder=51,
                                                                                                                                                                  ////  ///////////////////////////////
IsAutoCreateStatistics, IsAutoUpdateStatistics

--before change it has collation of my server:
<And would would be a simple test, within a sproc?>

create table tt (cv varchar(10))
create procedure usp_test1
select * from TT  -upper Case
exec  usp_test1

Msg 208, Level 16, State 1, Procedure usp_test, Line 3
Invalid object name 'TT'
alter procedure usp_test1
select * from tt

exec  usp_test1

---no error