Avatar of dirkmartin
dirkmartin
Flag 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?
Microsoft SQL Server

Avatar of undefined
Last Comment
EugeneZ

8/22/2022 - Mon
Raynard7

Hi,

This is possible

please see http://www.databasejournal.com/features/mssql/article.php/3302341 for a tutorial about how it is done

EugeneZ

try:

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

dirkmartin

ASKER
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.
===================================================

EugeneZ,

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 ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
EugeneZ

try:

ALTER DATABASE your_DB COLLATE sql_latin1_general_cp1_cs_as
dirkmartin

ASKER
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.
EugeneZ

<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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
EugeneZ

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)
dirkmartin

ASKER
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?
dirkmartin

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dirkmartin

ASKER
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?
ASKER CERTIFIED SOLUTION
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
EugeneZ

test:

create procedure usp_test
as
select getdate()

Go

exec usp_test
Go
exec Usp_test
       /\
EugeneZ



run
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:
Collation=SQL_Latin1_General_CP1_CI_AS
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
EugeneZ

<And would would be a simple test, within a sproc?>

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

result:
Msg 208, Level 16, State 1, Procedure usp_test, Line 3
Invalid object name 'TT'
---
--fix
alter procedure usp_test1
as
select * from tt
go

exec  usp_test1
go

result:
---no error