February 1, 2008 at 6:49 am
Hi all,
I have a big problem: I installed a default instance of SQL Server 2005 with a wrong collation. Now, I have all databases attached, replication restored, DTS packages converted to SSIS and jobs converted. Now, I want to change the collation to the correct collation because my ERP client throws errors about collation even that all databases were restored with the correct collation and are in compatibility level of 80.
I have seen (in BOL I think) that you could change the collation if you run SQL Server from command prompt but this is not my situation.
February 1, 2008 at 9:36 am
I'll tell you what worked for me, but there could be better ways. There was an instance where I created an instance of SQL Server using the default collation, but in reality I needed the case sensitive collation.
My case was easier than yours, because I could start from scratch again without too much headache. I scripted out the tables, removed any hard coded collation statements in the DDL, created a new DB with the collation that I wanted (collation can be set at the server, database, table(??), and column level), so that by default all of the varchar type columns would be created with the collation of the database, ran my DDL script, then transferred my data from the db with the wrong collation to the right one, and that seemed to sort me out.
I'm just remembering this off the top of my head, so I may have missed something.
Hope this helps.
February 3, 2008 at 1:06 am
Hi,
My databases are too large and complex to make some operations on the scratch. Anyway, I got on with change collation at database level in the following way: I have to drop all check constraints and all functions (which complaints about collation) and alter database with the new collation. Now, I have to figured out how to create the check constraints from the old database.
February 4, 2008 at 11:13 am
you can't change collation at server level. but you can change the collation on particular columns. also changing collation on the database only makes the collation effective for columns to be created later and not for ones already created.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 5, 2008 at 4:48 am
Hi,
I dropped the instance server 2005 and reinstalled withe correct collation. So, I solved the problem.:)
May 8, 2008 at 7:36 am
If you can't rebuild master for any reason..
Try the following ...
1. The function problem is related to passing back varchar/char/nchar/nvarchar as a table variable column.
You need to ensure that columns are declared with the keyword collate database_default..rather than nothing.
Example:
create function @mylist
returns table ( col1 int, col2 varchar(200) collate database_default )
as
.......................................
2. Once you have changed all functions... The attached script can be run to :
a. drop check constraints
b. change the collation
c. re-create check constraints.
May 8, 2008 at 7:52 am
Sugesh Kumar (2/4/2008)
you can't change collation at server level. but you can change the collation on particular columns. also changing collation on the database only makes the collation effective for columns to be created later and not for ones already created.
Actually, you can change the collation of the server. From BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3242deef-6f5f-4051-a121-36b3b4da851d.htm):
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. For more information, see Collation Settings in Setup.
Changing the Server Collation
Changing the default collation for an instance of SQL Server 2005 can be a complex operation and involves the following steps:
Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
Export all your data using a tool such as bulk copy.
Drop all the user databases.
Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
For more information about rebuilding the master database, see How to: Rebuild the Master Database for SQL Server 2005.
Create all the databases and all the objects in them.
Import all your data.
😎
November 10, 2008 at 8:34 pm
hi,
i'm interested to know more about applying collation sequence change at the server level, as discussed here ...
but want to ask how would de-tach/re-attached of database work here? or would that not work at all? would like to ask if the steps to detach/re-attach my (user) database/s (and apply the collation sequence at the database) could replace those for exporting / importing my database. so, what i'm asking is the following sequence of "updating" existing collation on my (default) sql server MSSQLSERVER instance from collation X to collation Y will work:
> de-attach my user database file
>rebuild master database with new collation Y, using setup command (you mentioned before):
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 .....
at this point i now have MSSQLSERVER with the new collation sequence (for system db, tempdb also)
> re-attach my user database file
> apply new collation sequence change to my user database
much thanks in advance for any feedback ...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply