December 12, 2007 at 1:50 am
Hi,
As Kumar said you need to rebuild system databases to change collation (in SQL 2000 you need just to Rebuild Master in 2005 the way to rebuild master has changed).
Check the link
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Regards,
Ahmed
December 13, 2007 at 1:08 am
Why should software (like TFS) depend on the collation of the server instance? A collation has no meaning unless used within the context of a database and starting with SS2K it has been possible to create databases whose collation is different from the instance collation. Unless of course TFS accesses the databases that get created on a new SS install.
In any case, when issuing the
alter database
on an existing database be prepared to drop and recreate constrainst that use varchar columns.
Has anyone had to drop and recreate anything else for a collate change to work?
December 13, 2007 at 4:17 am
Are you trying to change a default or named instance of sql server ? if it is not a default instance then you need to change the string MSSQLSERVER to the name of you instance
CodeMinkey
June 10, 2008 at 4:37 am
Hi
Yes I just had to drop table constraints and views. This is what I done.
Database Properties - selected single user mode
Script constraints and views to files
delete constraints and views
change collation to correct one
Database properties - selected MultiUser mode
recreate constraints and views.
Happy again
August 27, 2008 at 11:56 am
Oh Microsoft are so infuriating sometimes.
Why do you have to run this with /q?
Why can't they let you know where the install log files are saved?
Why can't they keep the list of switches somewhere handy so I don't have to go scouring the internet for it every damn time.
Geez!
August 28, 2008 at 9:18 am
i usuually uninstall and reinstall with correct collation. I believe you can't use your present databases in the new collation you are trying to re-create the instance with.
October 14, 2008 at 12:56 pm
I had the same issue. i have ran the script in a command prompt, but now it has been sitting for about 5 minutes. How long does it normally take to rebuild the database?
Thanks for any help.
January 9, 2009 at 4:18 am
HI
chmod
I have got the same problem in sql server
I tried Servaral Ways That i found in Goolgle
And I over come the problem in this way
That I created a NEw database With Defalut Collation then
I Altered the Database Collation (With out Entering any data or Creating Tables)
after changing collation i scripted all the objects to the new database
and just reinsterd the data
Now i am out the problem
Just try this
BUT This is a HARDWAY
January 9, 2009 at 5:53 am
chmod260 (3/28/2006)
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?
-----------------------------------------------------------------
You can not change sysstem database collastion. for that u have rebuild master database with new collation .
u can change collation of user database from database property--> Option--Collation
March 26, 2009 at 6:55 am
My thanks to everyone for posting in this thread. Its great to see I'm not alone.
Being the novice that I am, I'm praying that someone may have a simple solution to my collation problem. So here goes:
In management Studio
- I first connect to my Production SQL 2005 SP2 instance
- Next I 'right click' on the instance to display properties\general\server collation and there I see 'Latin1_General_Bin' collation
- So then I close 'properties', drill into 'Databases, and then expand my production database.
- Then I right click on my production database and choose 'properties', and BANG! I get an error message that reads:
---------------------------------
|->Cannot Show Requested Dialog.
|-> An exception occurred while executing a transact sql statement or batch. (Microsoft.SQLServer.ConnectionInfo)
|-> Cannot resolve the collation conflict between 'Latin1_General_Bin' and 'SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error 468)
---------------------------------
The next thing that I did was to check the SQL Instance Collation for our test sql environment which is located on a different physical server and sure enough, the Instance's properties\general\server collation was the same 'Latin1_General_Bin' as the collation of my production SQL instance.
- I then expanded the databases on the test instance, picked a random database and chose 'properties'.
- And again, the collation for that database was 'Latin1_General_Bin'.
----------------------------------
So at this point I'm led to believe that since my production instance and my test instance both have the same collation it must be my production database that has the collation mismatch.
So guys do you know of any way that I can make my production database collation match my production instance collation? Because as it now stands, I can’t view the properties of my production database.
Thanks so much to all of you for viewing this post.
CC
April 9, 2009 at 7:17 am
I to am having issues with rebuilding the master database. I keep gettign the error:
Please go to the Control Panel to install and configure system components.
Help!!!!!!!!!!!!!!!!! VEry frustrating!!!!!!!!!!!!!! I am trying not to reinstall the whole server.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
May 6, 2009 at 1:32 am
Its case sensitive. Use REINSTALL=SQL_Engine instead 🙂
June 30, 2009 at 8:31 am
"SQL_ENGINE" is case sensitive and needs to be "SQL_Engine".
July 1, 2009 at 8:15 am
I had the same issue for a long time with no avail. I didn't find the reinstall command useful and the alter database didn't help since it didn't change the tables collation. Every field that needs to be changed to a new collation and is attached to an index has to have the index dropped, the collation changed and the index recreated. That is a lot of work, and after searching for a long time, a friend of mine came with a very simple solution (undocumented), and I want to share with you.
1. Stop the running server.
2. Open a Command Prompt and go to the SQL Server directory.
3. Type:
sqlservr -m -T4022 -T3659 -q"new collation"
example:
sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"
The server will start in single user mode and change all databases with all tables inside, to the new collation.
PS: Sorry for my bad english (I'm from Brazil).
July 10, 2009 at 1:35 pm
Hi @ all
and also sorry for my badly english (I'm from Germany:-)).
My Problem:
Existing Databases with collation Latin1_General_CI_AS should
be attached into an SQL Server 2005 on an US-English System.
When I install with collation Latin1_General the Server uses the
collation SQL_Latin1_General_CP1_CI_AI (or _AS, can't remember,
it's 12 minutes ago... :cool:).
The collation change was required as an application shows a
message "...incompatible collations between server and database.
To fix the Problem I used the command (default instance):
start /wait setup.exe /qb REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD=useyourownpassword SQLCOLLATION=Latin1_General_CI_AS
Now the server use the wished collation Latin1_General_CI_AS.
My question:
Which collation I've to use whithin the setup to bypass the command?
Thanks in advance!
BTW:
du.pereira (7/1/2009)
1. Stop the running server.2. Open a Command Prompt and go to the SQL Server directory.
3. Type:
sqlservr -m -T4022 -T3659 -q"new collation"
example:
sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"
The server will start in single user mode and change all databases with all tables inside, to the new collation.
Looks very interesting, I'try this next week, Thanks!
Greetings
Sven
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply