How to change collation on MODEL ?

I have a 2005 SP1 server with the wrong server collation.
All user databases on this server have the right collation.
I am getting collation conflict errors when I use temp tables.
I believe this is because tempdb is created based on "model", and model has
the server collation.
I have tried to change the "model" collation using ALTER DATABASE, but I get
the error message "Cannot alter the database 'model' because it is a system
database.
"
How can I force tempdb to have a specific collation, instead of the same
collation as the server collation?> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
You have to rebuild the system databases, as you cannot have different collations across the system
databases. This is done using the SETUP program in 2005. This is described in
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?|||Hi Pat,
Thank you for your posting!
I agreed with Tibor. You need to rebuild the system database.
Also, here is an article for your reference.
325335 How to transfer a database from one collation to another collation
in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325335
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The doc mentions that all service packs and hotfixes are lost when rebuilding
the system databases, and need to be re-applied.
We have SP1 and several hotfixes, this sounds like a lot of work for just
changing the collation.
"Tibor Karaszi" wrote:
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
> You have to rebuild the system databases, as you cannot have different collations across the system
> databases. This is done using the SETUP program in 2005. This is described in
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
> >I have a 2005 SP1 server with the wrong server collation.
> > All user databases on this server have the right collation.
> >
> > I am getting collation conflict errors when I use temp tables.
> > I believe this is because tempdb is created based on "model", and model has
> > the server collation.
> >
> > I have tried to change the "model" collation using ALTER DATABASE, but I get
> > the error message "Cannot alter the database 'model' because it is a system
> > database.
> > "
> >
> > How can I force tempdb to have a specific collation, instead of the same
> > collation as the server collation?
>|||> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
Yep, that is why I always advocate to be very careful when selecting the collation during
installation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pat" <pat@.online.nospam> wrote in message
news:DFA53097-0EB5-4125-A9EC-E0EF157212D9@.microsoft.com...
> The doc mentions that all service packs and hotfixes are lost when rebuilding
> the system databases, and need to be re-applied.
> We have SP1 and several hotfixes, this sounds like a lot of work for just
> changing the collation.
> "Tibor Karaszi" wrote:
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>> You have to rebuild the system databases, as you cannot have different collations across the
>> system
>> databases. This is done using the SETUP program in 2005. This is described in
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/df40c888-691c-4962-a420-78a57852364d.htm.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Pat" <pat@.online.nospam> wrote in message
>> news:B4BB3864-D003-426A-9860-CC2FC80D0E06@.microsoft.com...
>> >I have a 2005 SP1 server with the wrong server collation.
>> > All user databases on this server have the right collation.
>> >
>> > I am getting collation conflict errors when I use temp tables.
>> > I believe this is because tempdb is created based on "model", and model has
>> > the server collation.
>> >
>> > I have tried to change the "model" collation using ALTER DATABASE, but I get
>> > the error message "Cannot alter the database 'model' because it is a system
>> > database.
>> > "
>> >
>> > How can I force tempdb to have a specific collation, instead of the same
>> > collation as the server collation?
>>|||"Pat" wrote:
> I have a 2005 SP1 server with the wrong server collation.
> All user databases on this server have the right collation.
> I am getting collation conflict errors when I use temp tables.
> I believe this is because tempdb is created based on "model", and model has
> the server collation.
> I have tried to change the "model" collation using ALTER DATABASE, but I get
> the error message "Cannot alter the database 'model' because it is a system
> database.
> "
> How can I force tempdb to have a specific collation, instead of the same
> collation as the server collation?
In addition to the other replies to your query, there may be another way to
do this.
If you create a new temporary table in a stored procedure, use the good
collation.
example:
Create Table #Status
(
State nvarchar(128) collate database_default
)
This might be a good solution if there are not too many places where
temporary tables are created.
hope this helps.
kind regards,
Bart.sql