MS SQL Server Collation setting

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 2008 Forums

SQL Server Administration (2008)

MS SQL Server Collation setting

Author

Topic

rameshkg123
Starting Member

3 Posts

Posted - 2011-09-07 : 00:08:09

Hi,

We have installed MS SQL 2008 R2. The collation setting which is present by default is SQL_Latin1_General_CP1_CI_AS.

I need a different collation setting namely SQL_Latin1_General_CP850_BIN2.

Please suggest as to how to change the MS SQL Server collation settings, without reinstallation of MS SQL.

I am new to MS SQL work and hence your input is appreciated.

Regards
Ramesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 00:18:03

[code]ALTER DATABASE <yourdbname>
COLLATE SQL_Latin1_General_CP850_BIN2;
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rameshkg123
Starting Member

3 Posts

Posted - 2011-09-07 : 00:29:51

Hi,

Thanks for the input. However, that allows change of a databases collation not the sql server instances collation. Please correct me if i am wrong.

Regards
Ramesh

quote:
Originally posted by visakh16

ALTER DATABASE <yourdbname>
COLLATE SQL_Latin1_General_CP850_BIN2;
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 01:25:01

you want to change all of collations at once?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rameshkg123
Starting Member

3 Posts

Posted - 2011-09-07 : 02:27:01

Hi,

Basically, i have installed MS SQL recently and i have not created any database. While installing the MS SQL, we can configure the collation settings, since, otherwise, it takes a default one. Now, i wnt to change that collation settings (not at the database level).

Regards
Ramesh

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-09-07 : 16:27:13

check this:

From BOL:

To rebuild system databases and specify a new system collation
Insert the SQL Server 2005 installation media into the disk drive.

Run the following command from the command prompt:

start /wait <DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.

Important The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the section below on REBUILDDATABASE for more information.

The /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files.

The /qb switch allows display of basic Setup dialog boxes. Error messages are also displayed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources