• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1186
  • Last Modified:

Changing COLLATION on SQL Server 2008

Hi Guys,
I need to change the SQL COLLATION on all databases on our production server (SQL SERVER 2008)

1.  Can this be done without reinstalling SQL Server 2008?
2.  I read this blog and was wondering if anyone has used the commands and will collation be changes system wide for all dbs in the database instance?

http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx
3. Are there risks attached to changing COLLATION that I should know about?


Thanks so much,
John
0
johnmc33
Asked:
johnmc33
  • 6
  • 5
1 Solution
 
geek_vjCommented:
>>I need to change the SQL COLLATION on all databases on our production server (SQL SERVER 2008)

Are you trying to do a server level collation change or user database(s) collation change?

If it is for only user databases, then it is pretty simple. However if it is server level collation change (ie master database collation change) then it requires rebuilding the master database.

>>Are there risks attached to changing COLLATION that I should know about?

This is a wide end question. Firstly, why are you trying to change the collation?

Collation change will typically change all the settings at SQL Server level like storing the data, data format etc

So, if you are absolutely sure about the collation change then only please proceed ahead.

If you need any clarifications, please revert.
0
 
cyberkiwiCommented:
Yes you do have to reinstall, but MS has provided a way to reinstall only changing the collation

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName

http://msdn.microsoft.com/en-us/library/ms179254.aspx

It is safe to use.

>> will collation be changes system wide for all dbs in the database instance?
No - you need to do this manually.  Not just the database collation, but also on all columns, and re-creation of all objects (proc, func, view) that have collation information.  See here for a way to do that and also a view of how big the task is.

http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

>> 3. Are there risks attached to changing COLLATION that I should know about?
Not really, unless you have apps from suppliers that specifically use ascent insensitive or case sensitive collations and you go re-collating their db to some new server-wide default.
0
 
johnmc33Author Commented:
cyberkiwi,

How do I find out current collation on my server ?
I will run the test and see what happens.

Risk: My client uses 1250 collation and we have built his server using Latin collation. He wants it changed. So my choice is ... reinstall SQL Server with correct collation and restore all databases.
or try to reinstall collations for each database ....

what do you think is best approach?

John

John
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
cyberkiwiCommented:
If by "uses 1250" you mean that his databases are already on that collation, then just change the server collation per the setup command.  However, if work has been done on the server for quite a while, you still cannot escape a rebuild/recollate of db objects, for example a create table without specifying collation uses the server default.

Restoring databases does not get rid of the problem - reinstall w/ collation change keeps all databases intact, but as usual, make full backups first - never trust computers.
0
 
cyberkiwiCommented:
--check collations of databases

select databasepropertyex('master','collation')
select databasepropertyex('mydbname','collation')

--check collations of columns

select o.name, c.name, c.collation_name, o.*
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
where c.collation_name is not null
  and o.type_desc not like '%System%'
  and o.type_desc not like '%INTERNAL_TABLE%'
0
 
johnmc33Author Commented:
Thanks,

I checked the collations ...

1.Master database has collation of SQL_Latin1_General_CP1_CI_AS ... this is what we've installed the database with.
2. checked one of the actual dbs and the collation is SQL_Latin1_General_CP1250_CI_AS ....  I restored these dbs to this SQL Server installation ...

client wants everything as SQL_Latin1_General_CP1250_CI_AS

So if I change collation using the setup command you detailed above ...  that should do the trick ?

John

John
0
 
johnmc33Author Commented:
If an instance of SQL Server is installed as  SQL_Latin1_General_CP1_CI_AS  collation but databases are restored as SQL_Latin1_General_CP1250_CI_AS  ....

which collation is used when requests are made to the db ?
0
 
cyberkiwiCommented:
Database default, unless it involves functions or temp tables - unless specified, the columns are returned collated server default; instant collation mismatch.
0
 
johnmc33Author Commented:
ok so I need to rebuild the master database as per command above correct?
0
 
cyberkiwiCommented:
That's correct
0
 
johnmc33Author Commented:
excellent support
0
 
johnmc33Author Commented:
Hi Cyberkiwi,

Sorry to bother you again.

Ran that command on the installation of SQL Server 2008 that needs collation changed ... I got this popup ...

Please go to the control panel to install and configure system components.

any idea why I would get this?

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now