March 28, 2006 at 5:22 pm
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?
-----------------------------------------------------------------
March 29, 2006 at 12:07 am
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
March 29, 2006 at 12:29 am
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
March 29, 2006 at 2:04 am
Try:
ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS
March 29, 2006 at 2:11 am
That allows change of a databases collation not the sql server instances collation.
March 29, 2006 at 2:32 am
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
March 31, 2006 at 1:33 am
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
April 2, 2006 at 7:48 pm
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.
April 12, 2006 at 11:11 am
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
August 10, 2006 at 2:27 am
Have you found the solution yet? I'm still having problems
August 10, 2006 at 2:39 am
Found the problem. You are using the wrong version of setup.exe
January 9, 2007 at 10:03 am
Hi i am using the above stated method to try and reinstall to correct server collation. the setup runs Ok up to chosen to upgrade and i continue. the problem is i then get a message that "an installation package for microsoft sql server2005 can not be found.try the installation again using a valid copy of the installation package 'sqlrun_sql.msi'"
I am use sql 2005 developer edition with service pack 1. is pack causing this file to be invalid. i am using the developer edditio install setup.
any help appreciated.
January 10, 2007 at 2:10 am
I have foun the solution. msi location is cached in the registry. Explained below.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=949298&SiteID=1
November 13, 2007 at 9:36 pm
Nope, no solution, but I think the problem was a bad install package.
November 13, 2007 at 11:00 pm
You cant change the collation of the server once installed. you need to reinstall with new collation.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 15 posts - 1 through 15 (of 76 total)
You must be logged in to reply to this topic. Login to reply