How to change default collate in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to change default collate in SQL

Author

Topic

enb141
Starting Member

14 Posts

Posted - 2004-06-24 : 17:29:39

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

38200 Posts

Posted - 2004-06-24 : 17:31:24

You can change the option on the database level with ALTER DATABASE, just use the COLLATE option.

Tara

enb141
Starting Member

14 Posts

Posted - 2004-06-24 : 18:37:54

What if I wanna change all my databases? I would like to know if there's a simpler way to do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-24 : 18:41:13

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

enb141
Starting Member

14 Posts

Posted - 2004-06-24 : 18:44:28

Thank you very much for your faster response, I'll try it and I'll let you know if worked flawlessly

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-24 : 20:13:14

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.

Lucien MATAR
Starting Member

1 Post

Posted - 2009-09-01 : 04:10:31

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

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-10-01 : 04:08:17

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 05:50:26

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.

abel.uchiha
Starting Member

2 Posts

Posted - 2013-12-18 : 03:04:02

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources