SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to change default collate in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

enb141
Starting Member

14 Posts

Posted - 06/24/2004 :  17:29:39  Show Profile  Reply with Quote
Hi I didn't know of the existence of collates in SQL so I build my database using the default one "SQL_Latin1_General_CP1_CI_AS" but I wanna use "SQL_Latin1_General_CP1_CI_AI" in my database, does anyone knows if that's possible?

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 06/24/2004 :  17:31:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can change the option on the database level with ALTER DATABASE, just use the COLLATE option.

Tara
Go to Top of Page

enb141
Starting Member

14 Posts

Posted - 06/24/2004 :  18:37:54  Show Profile  Reply with Quote
What if I wanna change all my databases? I would like to know if there's a simpler way to do that?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 06/24/2004 :  18:41:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you mean for the whole server, then:

quote:


Changing Collation Settings After Installing
--------------------------------------------
Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure of all Microsoft® SQL Server™ 2000 databases. To change one or more of these settings, you must rebuild the master and user databases.





quote:


How to rebuild the master database (Rebuild Master utility)
-----------------------------------------------------------
To rebuild the master database

Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.


In the Rebuild Master dialog box, click Browse.


In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.


Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.



Note To continue, you may need to stop a server that is running.





Information taken from SQL Server Books Online.

Tara
Go to Top of Page

enb141
Starting Member

14 Posts

Posted - 06/24/2004 :  18:44:28  Show Profile  Reply with Quote
Thank you very much for your faster response, I'll try it and I'll let you know if worked flawlessly

Edited by - enb141 on 06/24/2004 18:44:53
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/24/2004 :  20:13:14  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Do you have any user databases installed on this database yet? If you do, there's significantly more work to do then just running the rebuildm utility. That will basically rebuild your system databases with the new collation. Unfortunately, you will still need to alter it for each database. You will then need to run a script that alters it for each column and index in the user databases, which is just loads of fun. Let us know how the conversion goes. If you need some help, I think I have some scripts buried away in one of my troves.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Lucien MATAR
Starting Member

Lebanon
1 Posts

Posted - 09/01/2009 :  04:10:31  Show Profile  Reply with Quote
I had the same problem; After creating a big number of tables with thousands or columns i realized that the collation was wrong so i wrote the following code to change the column collations:
This script generates the "Migration" script in the output, then copy paste and run
;)

------------------
Declare @TableName varchar(1000)
Declare @qry varchar(8000)
Declare @collation Varchar(200)
set @collation = 'COLLATE SQL_Latin1_General_CP1_CI_AS'


DECLARE Table_Cursor CURSOR FOR
select name from sysobjects where type = 'U' and name <> 'dtproperties' order by name
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--****************************************************************
DECLARE Query_Cursor CURSOR FOR
SELECT 'Alter table '+@TableName collate SQL_Latin1_General_CP1_CI_AS+' Alter column '+ sy.name + ' '+ ms.Name +'('+ cast(sy.length as varchar(20)) +') '+@collation as _qry
FROM dbo.syscolumns sy
join master..systypes ms on
ms.xType = sy.xType
where id = OBJECT_ID(@TableName) and ms.Name = 'varchar'

OPEN Query_Cursor
FETCH NEXT FROM Query_Cursor
INTO @qry
WHILE @@FETCH_STATUS = 0
BEGIN
print @qry
print 'GO'

FETCH NEXT FROM Query_Cursor INTO @qry
END
CLOSE Query_Cursor
DEALLOCATE Query_Cursor
--******************************************************************

FETCH NEXT FROM Table_Cursor INTO @TableName
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor






Lucien MATAR
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 10/01/2009 :  04:08:17  Show Profile  Reply with Quote
Hi All recently i got one information about this..related to sql 2005 version only.....

In SQL Server 2005 you can now do this in a few easy steps, the following is from MSDN:

1.Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
2.Export all your data using a tool such as bulk copy.

3.Drop all the user databases.
4.Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
5.Create all the databases and all the objects in them.
6.Import all your data.
Well, the steps aren't exactly easy and it might be a lot easier to install SQL Server with the correct collation to begin with. But when a friend calls with this questions, this is the answer.


I Have question on point 1 and 2..is it easy to take back up of these (user) databases..and after changing collation we can restore back to server????

T.I.A

Edited by - under2811 on 10/01/2009 04:09:31
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 10/01/2009 :  05:50:26  Show Profile  Visit webfred's Homepage  Reply with Quote
I think that's not the way because the restored databases will come up with the old collation...
The idea with backup/restore is so obvious so I don't believe someone is writing that things under points 1 and 2 if not necessary.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

abel.uchiha
Starting Member

2 Posts

Posted - 12/18/2013 :  03:04:02  Show Profile  Reply with Quote
it's shown "please go to control panel to install and co`nfigure system components" when i tried thi´s step..
what should i do to change the collation?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000