July 19, 2005 at 2:41 pm
Way back, our server collation was set to Latin1_General_Bin to satisfy the requirement of some software running on that server. Since then, we've been using that server for other software that requires SQL_Latin1_GeneralCP1_CI_AS. Since most applications we now have use SQL_Latin1_GeneralCP1_CI_AS, I would like to change the server default to SQL_Latin1_GeneralCP1_CI_AS, and leave that 1 original DB as Latin1_General_Bin.
What's the best way to do this ?
July 20, 2005 at 4:39 am
Assuming you are using SQL Server 2000 (SQL 7.0 doesn't support multiple collations on the same server), use rebuildm to rebuild the master database, specifying the new default collation. Beware, though - this will change the collation of all system databases and could cause you problems if, for instance, your application creates temporary tables in tempdb and attempts to join them with tables from the original database. Best to leave things as they are if everything is working at the moment.
July 20, 2005 at 8:04 am
John:
I have the same issue with one of my servers. The original use of the server was to host a datawarehouse generated from our mainframe.
However, overtime I've been asked to host several vendor applications which do not have the same collation.
I find it better to simply run an alter after the application insatll has setup the database. Or if the database is created from scratch, just use the drop down list and pick the desired collation.
USE master
ALTER DATABASE database
COLLATE < collation_name >
"If it ain't broke....."
JerseyMo
July 20, 2005 at 10:32 am
Just an FYI for those attempting to use REBUILDM.
You need to be logged directly onto the console. It will not work via an RDP connection. Although, if you have access through a Lights Out interface, you may have success.
July 21, 2005 at 1:00 pm
In SQL Server 2000 you don't need that, because you can add the collation to your create database and Table statement. Rebuild was a requirement for SQL Server 7.0. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
May 26, 2006 at 4:28 am
You can choose the database collation in 2K according to your requierments. RebuildM.exe will wipe out all your logins, DTS packages etc. TBH you are better off changing the default collation and then converting your DB (painful, but worth it in the end). Having a master, or more of a pain, a tempdb at a different collation to your data database will cause you to jump through more hoops that you really want to (yup, this is the battle scarred voice of experience here). BTW has anyone gotr a full list of collations and what the are (for example, what is the code page of latin1_General_bin and is it unicode compliant?)
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
May 26, 2006 at 11:14 am
I am not sure if you can use the list with SQL Server 2000 but the links below is the complete code page and collation listing for SQL Server 2005. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms144250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms180175(SQL.90).aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
November 1, 2006 at 9:04 am
I second (third) the trauma of different collations in tempd and operational databases. Its a right pain in the jacksy.
Backup your logins, and rebuild away.
Just remember there is a good chance that any database not in the server collation will suddenly start throwing errors...!
November 2, 2006 at 2:43 pm
You can change collation for a database and a column. But if you would like to set your new collation as default, you have to rebuild master database.
May 17, 2007 at 1:10 am
i tried rebuildm.
but the sql_latin1_general_cp1_ci_as collation required couldnot be found.
September 29, 2009 at 7:30 am
I'm trying to change the default server collation, but I don'k know exactly the way to do it.
When I execute rebuildm, it throws me an error.
could anyone help me??
March 8, 2010 at 4:15 pm
Another FYI...
If you're in a situation where you want to consolidate all your collation settings after rebuilding your master, here's an article I wrote for SQLAuthority, including all the source scripts for automating the collation changes. These script capture everything all the way down to column level.
The only caveat is that these scripts cannot handle Statistics, because Stats can't be scripted out - or at least I haven't been able to find a way to do so.
Hope this is helpful to some.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply