| Author |
Problem with Collation on SQL 2000
|
|
| robin9876@hotmail.com 2006-02-03, 11:23 am |
| On a live SQL Server 2000 in the last week I have noticed a problem
with collation with the Model and tempdb both changing from
'Latin1_General_CI_A
S' to 'SQL_Latin1_General_
CP1_CI_AS'.
I am not aware of anybody changing these settings. Do the any of the
log files record when these values have changed?
This has stopped some stored procedures that use the temporary database
from working and being able to use SQL EM to add users.
>From various other postings it seems that the system tables would need
to be rebuilt.
Are there detailed instructions available about how to use this tool
and restoring all the databases and SQL jobs?
| |
| lutzdw 2006-02-03, 1:23 pm |
| What is the collation of the other System databases? If they are different
than model, I would guess that someone changed the collation of the model
database.
If so, I would look for a backup of the model database and restore it under
an assumed name, like model_ttt, to try and confirm that the collation
changed for model. Since tempdb is rebuilt from the Model DB every time the
SQL instance is restarted, Tempdb would have picked up the collation change
when your server was last rebooted.
That's my 2 cents...
| |
| Scott Morris 2006-02-03, 1:23 pm |
| > On a live SQL Server 2000 in the last week I have noticed a problem
> with collation with the Model and tempdb both changing from
> 'Latin1_General_CI_A
S' to 'SQL_Latin1_General_
CP1_CI_AS'.
> I am not aware of anybody changing these settings. Do the any of the
> log files record when these values have changed?
I don't believe so. However, tempdb is recreated when sql server is
started. Therefore, a change to the default collation of model would
automatically propagate itself into tempdb with the next restart of the
server.
> to be rebuilt.
No - you shouldn't need to do this at all. Just change the default
collation for model back to the expected setting and then restart sql
server. Next - figure out who changed the default collation for model.
The bigger issue is related to persons making changes without understanding
the consequences - and that no one else knew about this change.
| |
| robin9876@hotmail.com 2006-02-06, 9:23 am |
| When running the command to change the collation you get an error
message showing that you can not change the collation value for a
system database.
Can any values in some system tables be directly updated?
| |
| Scott Morris 2006-02-06, 9:23 am |
| > Can any values in some system tables be directly updated?
Apparently changing model is more involved. There are directions (as the
other poster indicated). Prior to doing that, you might want to figure out
what changed when - perhaps something was changed in a user database
(accidentally perhaps) that can be more easily corrected.
|
|
|
|