different Collation Settings between Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

different Collation Settings between Databases

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-30 : 08:52:54

JM writes "Hi,

How is it Possible to change the collation Settings of an SQL 2000 instance that has already been installed. (during installation SQL didn't ask about the collation Settings).
The OS where the SQL Server works is Windows XP professional. The SQL server is an Personal Edition.

Collation of Userdatase SQL_Latin1_General_CP1_CI_AS (correct one!!)
Collation of SQL Instance (the systemdb's also) Latin1_General_CI_AS.

I hope to get a reaction soon.

Thanks,"

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-05-30 : 10:19:07

ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

note the COLLATE option.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-17 : 10:33:56

Good luck with this. I've just run into exactly the same problem, and on a cleanly installed sql 2K EE, I tried to use:

ALter database northwind collation sql_latin1_general_cp1_ci_as

and had several error messages re: constraint's, with a failed database alter at the end. Now, if the base northwind won't change , what's the chances I'm doing it to a 700 tables user db ?

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-17 : 10:55:44

UPDATE :

Hi there,

refer the follwing link :

http://www.microsoft.com/sql/techinfo/tips/administration/changingcodepage.asp

I have a collation problem and have found that it has been cauised by a Clean install of SQL 2K, followed by attaching db's from SQL 7. Thing is, the SQL_LATIN1_CP1_CI_AS collation that they refer to iss the default SQL7 collation, near as I can see - so I struggle to see how they justify classifying it as "legacy, self-defined code page" that they want to move away from. Also, the SQL 2K installation doesn't offer this collation in install! I'm going to have to re-uninstall SQL2K (3rd time today) then install SQL 7, then upgrade !

*sigh*

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-17 : 12:18:25

Maybe if you changed the database compatibility level first then performed the alter, maybe that would work.

Tara

Argyle
Yak Posting Veteran

53 Posts

Posted - 2003-03-17 : 15:24:54

Note that the collations that begin with "SQL_" are SQL collations to be used for backward compability with SQL 7. The other type of collations available is Windows collations and are new in SQL 2000.

When installing SQL 2000, and you want backward compability with SQL 7 you need to select the SQL collation called "SQL_Latin1_General_CP1_CI_AS" and not the Windows collation called "Latin1_General_CI_AS".

For example I'm in the process of migrating SQL 7 databases with swedish sortorder to a new SQL 2000 server. To avoid collation problems (for example with tempDB) I have to install the SQL 2000 with the SQL collation called "SQL_SwedishStd_Pref_Cp1_CI_AS" and not the Windows collation called "Finnish_Swedish".

Edit:
Forgot to answer the original question. Here is a link describing some ways to to change collation:
http://www.databasejournal.com/features/mssql/article.php/2013741

Note that it takes quite a lot of work if it's not a clean install and you already have a number of databases with wrong collation.

/Argyle

Edited by - argyle on 03/17/2003 15:28:32

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-18 : 02:54:36

Argyle,

I'll review the linked article.

While doing a SQL 2K EE install, unless my network copy is somehow corrupted, there is no SQL_ collation's listed. The install allows for Latin1_general, several others, and then list several "legacy" code pages - amongst them there are: "dictionary sort, case sensitive, code page 1252" etc. No where that I can find which of these is the SQL_Latin1_General code page.

Based on the link I supplied to the MS site, I am going to do a SQL 7 EE, SP2, SQL 2K EE upgrade, SQL 2K SP2, patch (vendor software, we can go to SP3).

PS - it *looks* like this problem may be only only SQL 2K EE , but I'll test and let ya know ...

Ciao

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-18 : 05:03:45

Alright, an update/solution (for my problem - apply as fits to your's).

Having had some nice server issues - server hung during SQL install, sql refused to continue install after reboot, and refused to uninstall due to unistal,isu being corrupted (forget the exact message, but that was it, basically). A registry rebuild got me a sQL running, but not usuable, so eventually had server rebuilt.

Did a SQL 7 EE install, SP2 install, SQL 2K EE upgrade, it kept the old "SQL_Latin1 collation" !! Did my SQL 2K SP2, fixed on or 2 minor problems, and we're up and running.

I'll update on the SQL 2K Std. Ed Collation when I get a chance.

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Apollois
Starting Member

49 Posts

Posted - 2004-02-08 : 22:53:01

Wanderer,

I think I may have a solution/clarification to this:

quote:
Originally posted by Wanderer
While doing a SQL 2K EE install, unless my network copy is somehow corrupted, there is no SQL_ collation's listed. The install allows for Latin1_general, several others, and then list several "legacy" code pages - amongst them there are: "dictionary sort, case sensitive, code page 1252" etc. No where that I can find which of these is the SQL_Latin1_General code page.

I just did a clean install of SS2K Developer Edition on a new machine. The default collation selection was "Dictionary order, case-insensitive, for use with 1252 character set." I accepted the default selection.

After applying SP3a, I restored a DB BU from SS7. I did NOT know what the collation is/was of the SS7 DB.

Now when I check the DB collation on the SS2K using sp_helpdb, it reports:

master -- Collation=SQL_Latin1_General_CP1_CI_AS
MySS7DB -- Collation=SQL_Latin1_General_CP1_CI_AS

In fact, it shows this same collation for DBs.

When I check the Server collation using sp_helpsort, it reports:

"Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data"

When I run SERVERPROPERTY ('Collation') it reports:
"SQL_Latin1_General_CP1_CI_AS"

So, it appears to me that:
1. The SS2K default collation of "Dictionary order ... 1252 character set." is the same as "SQL_Latin1_General_CP1_CI_AS"
2. This is also the default collation of SS7.

So, by default, SS7 and SS2K collations are the same. (I got lucky!)

However, this is in contridiction with a MSFT Article:
http://www.microsoft.com/sql/techinfo/tips/administration/changingcodepage.asp
which states:
"I've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but the SQL Server 2000 default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?"

Somebody who really knows what's going on needs to document all of this much better.

Best Regards,
Jim

bponsen
Starting Member

6 Posts

Posted - 2004-05-18 : 09:37:57

Hello Everybody,

If I may cut in: I'm working on a similar problem; We are working with three SQL servers containing 10 databases in total; The SQL server collations need to be changed for which I was planning a re-install of the servers. After that I would need to attach the original user databases that have mixed collations in database and table levels in such a way that all collations refer to Server default.
I have seen the Alter database and alter table commands but I cannot find the correct way to set the table and DB collations to "Server Default", so that they indeed use the server defaults as they get attached to the newly installed server.
I would like to prevent having an actual restore of data into a new tableset as I have over 550 GB of data available which we found out earlier takes 2.5 days to restore.
Any tips or tricks in this?

Much appreciated,
CU,
Bart.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 10:55:13

Have a look at this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27155&SearchTerms=collation
Not really the same problem but might give you ideas.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mahesh
Starting Member

6 Posts

Posted - 2004-05-18 : 14:44:13

You can do it.For that you have to run rbuild.exe on your machine.It resides in microsoft sql server files directory.It takes serveral hours to complect rebuilding.For more information read
books online.

Mahesh Paranjpe
SQL DBA

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 16:20:30

It's Rebuildm.exe
It will rebuild the system databases but not the user databases.
After that you still need to recreate the databases. I usually think it's better to re-install.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bponsen
Starting Member

6 Posts

Posted - 2004-05-19 : 05:12:37

Thank you guys. For a good laugh hear this.
Instead of correcting the problem by getting the databases consistent Development decided to create a fix on every stored procedure in our software that create temp tables to include the required collation for the purpose. Hmmm, I'm very curious how this will effect our upgrading plans and customization roll out.
The comparisons between these tempDB tables were causing the collation conflicts in the first place, so I do understand why they say this.

until next time,
Bart.

Kristen
Test

22859 Posts

Posted - 2004-05-20 : 09:30:46

quote:
Development decided to create a fix on every stored procedure in our software that create temp tables to include the required collation for the purpose.

If there is a chance that a database will wind up on a server which has a different collating sequence I reckon this is essential.

We put explicit COLLATE on all the [varchar/text column] definitions of CREATE TABLE (Application database, TEMPDB etc).

Might be an idea to run CREATE scripts in a DB with an "alien" collation order and then do a search of SYSCOLUMNS for different collations to find any that have slipped through the net.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources