| Author |
Topic |
|
jones_d
Yak Posting Veteran
61 Posts |
 Posted - 2005-01-25 : 13:28:47
|
| Hi,Is it possible to change the collation of the tempdb database on SQL Server 2000 without having to do a reinstall of SQL Server? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-25 : 15:11:12
|
| Have you tried using the COLLATE option of the ALTER DATABASE command?HTH=================================================================Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-26 : 06:00:17
|
| Is this 'coz you've got some TEMP tables and they are wrongly collated compared to the DB they are running in?If so you can create them with COLLATE statements on each column-create line (Text/Varchar/Char columns only) and that will make that particular problem go away ...Kristen |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-26 : 06:01:08
|
| I haven't tried anything yet. From what I've been reading, in order to change the COLLATION of tempdb one should resinstall SS or else rebuild the master database.I would ideally like to avoid having to do a reinstall or a rebuild but is it okay to use the ALTER DATABASE command to change the COLLATION of the tempdb database? I know it's fine for user databases but not so sure about system databases. |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-26 : 06:04:52
|
| Hi Kristen,Thanks for suggestionYep - problem is caused by temp tables. Unfortunately I can't specify the collation at the column level because the queries are generated automatically by a DSS tool querying the database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-26 : 14:41:54
|
| I did the "rebuild MASTER" thing once to change a collation. It was a complete nightmare - but it did work. I expect if I did it once a week it would be worth doing ... but for the one-off that I did I reckon that an install from-scratch would have been much quicker and less hair-pulling!Assuming that MASTER and MSDB probably use TEMPDB there may be no way to change TEMPDB only - but I don't know that for sure.Kristen |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-27 : 05:06:10
|
| Thanks Kristen - that's good to know. Did some research on web also and it does seem that a reinstall is much easier than rebuilding the master db.We are considering changing the collation of our SQL 2000 database from Latin1_General_CI_AS, which appears to be the Microsoft default during installation, to SQL_Latin1_General_CP1_CI_AS, which will allow our application to work.Are we taking a step backward by doing this? / Is the Latin1_General_CI_AS collation the way forward?Is it likely that we will face problems with other applications in the future if we change the collation to SQL_Latin1_General_CP1_CI_AS? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-27 : 06:42:55
|
| Change the collation of the model database and restart your SQL Server. tempdb is built from model with each restart.WARNING: ALL databases are built from model. Keep this in mind when creating new databases.With SQL Server 2000, you rarely have to rebuild master, especially to fix collation issues. |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-27 : 08:07:36
|
| Thanks robvolk,Can I change the collation of the model db using the ALTER Database command? The master database will then have a different collation to the model and tempdb - Does this make any difference? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-27 : 08:28:53
|
| Nope, not a problem. model is just that, it's only a model for other databases. You should not use model for any real database work.You just need to keep collations in mind when you have different databases interact. And almost all statements, clauses, and functions can now accept a COLLATE clause in case you have to specify one that's different from the default. |
|
|
|
jones_d
Yak Posting Veteran
61 Posts |
Posted - 2005-01-27 : 11:54:15
|
| Tried the following command:ALTER DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS but I got this error:Server: Msg 3708, Level 16, State 5, Line 1Cannot alter the database 'model' because it is a system database.I have administrative rights. Should I change the collation of the model db some other way? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-27 : 19:20:18
|
Well, that's kinda messed up because I know (cause I've done it) that you can alter the size, recovery model, and a few other things on model. BOL states very clearly under "ALTER DATABASE":quote:
The default recovery model is determined by the recovery model of the model database. To change the default for new databases, use ALTER DATABASE to set the recovery option of the model database.
So I guess that you can't change the collation but you can change other things. |
|
|
|
|
Changing collation of tempdb database