Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to change SQL Server Collation Expand / Collapse
Author
Message
Posted Tuesday, March 28, 2006 5:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 28, 2014 11:36 AM
Points: 5, Visits: 102
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
Post #269173
Posted Wednesday, March 29, 2006 12:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:52 AM
Points: 123, 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




Post #269239
Posted Wednesday, March 29, 2006 12:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:52 AM
Points: 123, 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




Post #269245
Posted Wednesday, March 29, 2006 2:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 2, 2007 6:43 AM
Points: 62, Visits: 1

Try:

ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS

 

 

Post #269260
Posted Wednesday, March 29, 2006 2:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:52 AM
Points: 123, Visits: 5
That allows change of a databases collation not the sql server instances collation.


Post #269262
Posted Wednesday, March 29, 2006 2:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 27, 2010 5:19 PM
Points: 455, 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

Post #269273
Posted Friday, March 31, 2006 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 28, 2014 11:36 AM
Points: 5, Visits: 102
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
Post #270044
Posted Sunday, April 2, 2006 7:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:52 AM
Points: 123, 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.




Post #270410
Posted Wednesday, April 12, 2006 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 8:30 AM
Points: 4, 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
Post #272778
Posted Thursday, August 10, 2006 2:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2008 3:24 AM
Points: 2, Visits: 2
Have you found the solution yet? I'm still having problems
Post #300788
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse