SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 different Collation Settings between Databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/30/2002 :  08:52:54  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 05/30/2002 :  10:19:07  Show Profile  Reply with Quote
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.
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 03/17/2003 :  10:33:56  Show Profile  Reply with Quote
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!
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 03/17/2003 :  10:55:44  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37163 Posts

Posted - 03/17/2003 :  12:18:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
Maybe if you changed the database compatibility level first then performed the alter, maybe that would work.

Tara
Go to Top of Page

Argyle
Yak Posting Veteran

53 Posts

Posted - 03/17/2003 :  15:24:54  Show Profile  Reply with Quote
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
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 03/18/2003 :  02:54:36  Show Profile  Reply with Quote
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!
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 03/18/2003 :  05:03:45  Show Profile  Reply with Quote
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!
Go to Top of Page

Apollois
Starting Member

USA
49 Posts

Posted - 02/08/2004 :  22:53:01  Show Profile  Reply with Quote
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
Go to Top of Page

bponsen
Starting Member

Netherlands
6 Posts

Posted - 05/18/2004 :  09:37:57  Show Profile  Send bponsen a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/18/2004 :  10:55:13  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

mahesh
Starting Member

India
6 Posts

Posted - 05/18/2004 :  14:44:13  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/18/2004 :  16:20:30  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

bponsen
Starting Member

Netherlands
6 Posts

Posted - 05/19/2004 :  05:12:37  Show Profile  Send bponsen a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/20/2004 :  09:30:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000