changing the SORT Collations

H
I am very new in SQL2000. My question is , is it possible to change the sort collation in SQL2000 after installation and how. Currently my server is set to case sensitive. how do I change it to case insensitive?
Also is it possible to do this on my select command instead? For instance, my server is case sensitive, but what i want to do is when I do a select command, I would temporarily remove the case sensitivity so it can get the desired results. This setting is only temporary and it goes back to the original setting once the session is diconnected. Is there a way to do this
Thank youCollation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.
In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.
To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>
To alter a table, you can change the characters columns with ALTER TABLE
ALTER TABLE MyTable ALTER COLUMN ColumnName
[varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]
Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||The collation on the sql server 2000 is database-specific. That means you
can create your application database with any collation you want.
To do that use, for exaple,
CREATE DATABASE dbname
COLLATE SQL_Latin1_General_CP1_CI_AI
If you want your database with one collation, having, for example, case
insensitive, and you want that one field of one table to be case sensitive,
you can do that by using:
CREATE TABLE mytable (
ID int primary key identity(1,1),
NAME nvarchar(256) COLLATE SQL_Latin1_General_CP1_CS_AI
But, if you want to rebuild your master database (that defines your system
tables collation), you can use the rebuild master using a utility.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rebldmst/rebldmst_24aa.asp.
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||Thanks andy and francisco for the info!!