SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to change SQL Server Collation


How to change SQL Server Collation

Author
Message
chmod260
chmod260
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 114
Hello, this message was previously posted in the General Discussion and got no answer. Perhaps this is the correct place...

I was trying to install Visual Studio Team Foundation Server but the final check failed because my SQL Server 2005 installation had the wrong Collation setting:

-------------------------------------
The System Health Check has detected a problem that will cause Setup to fail.

Description
SQL Server collation is not set to one supported by Team Foundation Server.

Workaround / Remedy
The SQL Server collation is not set to one supported by Team Foundation Server. Change your collation to one that is supported and run setup again.
-------------------------------------
I googled around for a while and found that TFS requires a collation that is case insensitive and accent sensitive. My collation was accent INsensitive.(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275420&SiteID=1)

To change Server Collation in SQL Server 2000 I usually use the rebuildm file but this is not supported anymore in SQL 2005.

Microsoft recomends setup.exe to rebuild the master database (http://msdn2.microsoft.com/en-us/library/ms143269(SQL.90).aspx)
and Tom Wisnowski details the operation (http://geekswithblogs.net/mskoolaid/archive/2005/12/17/63413.aspx)

I tried this solution but... the master database was rebuilt but no change to change the collation (at least within a visible GUI).

Question: How do you change the SQL Server 2005 Collation? Do you MUST rebuild the master database?

-----------------------------------------------------------------


Regards,
chmod
dharper3
dharper3
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5

Hmm can't say I've tried on 2005 either. One way would be via add / remove programs uninstall sql server db engine and re-install. During install you need to ensure you choose a windows collation thats suitable for TFS, not sure of your region settings on the server but a suitable one would be Latin1_CI_AS.

If I have time I might try to mod one here and advise if the add/remove allows a modification / upgrade to the colation or not.

Derek





dharper3
dharper3
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5

Straight from BOL, the source of all knowledge:

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.

Hope this helps.

Derek





bubu Shtef
bubu Shtef
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 1

Try:

ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS


dharper3
dharper3
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5
That allows change of a databases collation not the sql server instances collation.



raj-247939
raj-247939
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 229

The server collation acts as the default collation for all system databases that are installed with the server, and also any newly created user databases. The server collation is specified during setup

If u want to change the collation u have to rebuilt the master database.

If u dont want to do that then u can do it like this (reinstall sqlserver)

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

from

Killer


chmod260
chmod260
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 114
Thank you to all!

SQLCOLLATION= was de missing part of the equation...

Also the ALTER DATABASE MyDB COLLATE NewSystemCollation> tip is very interessting as a per database change... (does it work win sql 2000?)

Thanks


Regards,
chmod
dharper3
dharper3
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 5

Yes sql2000 supports multiple collations 'each of which can be set to a different collation' at the sql instance level, the database level, the table level and columns within a table.

By default if you specify no collation on a create db, the database takes on the instances default collation (its actualy based on whatever the model db' collation is), objects within a db (again if you do not specify) take on the databases default collation. You can specify a different collation for any of these objects.

Note if you perform a db restore the collation of the database will be retained. i.e backup a db and its collation was 'latin1_general_CI_AS', then restore to sql instance which has a default of 'sql_LATIN1_CP1_CI_AS' and the database retains its collation of 'latin1_general_CI_AS'. Something to be aware of when restoring vendor supplied databses etc onto your systems.





BPv20
BPv20
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 25
Hello All -

I am having a problem rebuilding the master db (for a collation change) on an installed SQL 2005 instance. I am recieving the error: "The installer has encountered an unexpected error. The error code is 2711. The specified feature name ("SQL_ENGINE") not found in feature table.

I have put the current instance in single user mode via the sqlservr -m command and executed the command:

start /wait E:\media\SQL2k5\SQLENTSEL_2K5\Servers\setup.exe INSTANCENAME=MSSQLSERVER REINSTALL=SQL_ENGINE REBUILDDATABASE=1 SAPWD=XXXXXXXX SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Unfortunately, I can't find much info on this error. My last resort is going to be to add/remove via the control panel, but haven't gotten there yet. any suggestions?

B
Roger Williams-357196
Roger Williams-357196
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 2
Have you found the solution yet? I'm still having problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search