My master and tempdb are in Latin collation, but all my other
databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
queries that require the usage of tempdb:

SELECT a.*
FROM (SELECT * FROM mydb..Table_a) as a
INNER JOIN mydb..table_b
ON a.field1 = b.field2

Since the tempdb collation is different than mydb, it will generate an
error: “Cannot resolve collation conflict for equal to operation“, and
I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.

Any ideas how to change the tempdb collation? Any implications from
doing so? How will the master and msdb be affected? Do they also
need to change? What are the steps to change the tempdb to a new
collation?

See the following quote from this MS whitepaper
http://download.microsoft.com/downlo...ionChange.docx :

<quote>
Changing the tempdb Collation

The collation of tempdb cannot be changed by using the ALTER DATABASE
statement—SQL Server does not allow this since tempdb is part of the system
database. Note that tempdb uses the collation of the model database. Since
there is a way to change the collation of the model database, we inferred
that we should be able to change the collation of tempdb. Recall that the
model database can be backed up and restored. So, for example, if we have
another instance of SQL Server running with a default collation of
French_CI_AS, we can back up the model database from the “French� server,
restore it on the target server, and then restart the MSSQL service on the
target server. We used this technique to change the collation of tempdb for
the test cases described in the previous section.
</quote>

Linchi

"shija03" wrote:

> My master and tempdb are in Latin collation, but all my other
> databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
> queries that require the usage of tempdb:
>
> SELECT a.*
> FROM (SELECT * FROM mydb..Table_a) as a
> INNER JOIN mydb..table_b
> ON a.field1 = b.field2
>
> Since the tempdb collation is different than mydb, it will generate an
> error: “Cannot resolve collation conflict for equal to operation“, and
> I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.
>
> Any ideas how to change the tempdb collation? Any implications from
> doing so? How will the master and msdb be affected? Do they also
> need to change? What are the steps to change the tempdb to a new
> collation?
>
>

If instead of changing the tempdb collation, you start using COLLATE
DATABASE_DEFAULT right now, you will be safe for the rest of your life and
you will be able to put your database on any server without any concerrn.
If not, then sooner or later, this will come back hanting you; possibly at
the worst moment.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"shija03" <shija03@gmail.com> wrote in message
news:b1d2ebd0-ff7c-4023-8a0a-f12bf32e2e38@27g2000hsf.googlegroups.com...
My master and tempdb are in Latin collation, but all my other
databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
queries that require the usage of tempdb:

SELECT a.*
FROM (SELECT * FROM mydb..Table_a) as a
INNER JOIN mydb..table_b
ON a.field1 = b.field2

Since the tempdb collation is different than mydb, it will generate an
error: “Cannot resolve collation conflict for equal to operation“, and
I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.

Any ideas how to change the tempdb collation? Any implications from
doing so? How will the master and msdb be affected? Do they also
need to change? What are the steps to change the tempdb to a new
collation?

You face the same situation as every international user (non-US) of SQL Server is facing.


<<I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.>>

Above *is* the reliable and responsible way to handle this.

Your other option is to make sure that tempdb has the same location as your user database(s). If you
don't control the installation (if the installation is done at some of your customer sites, for
instance), then you need to be *very clear* about this in your installation manual.


<<Any ideas how to change the tempdb collation?>>

Rebuild the system databases or reinstall.


<<Any implications from doing so?>>

Everyting in the system databases will be lost.


<<How will the master and msdb be affected? Do they also
need to change? What are the steps to change the tempdb to a new
collation?>>

See above...
You can't have a different collation for tempdb (which is drawn from model) than master/msdb. Such
config is not supported.


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"shija03" <shija03@gmail.com> wrote in message
news:b1d2ebd0-ff7c-4023-8a0a-f12bf32e2e38@27g2000hsf.googlegroups.com...
My master and tempdb are in Latin collation, but all my other
databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
queries that require the usage of tempdb:

SELECT a.*
FROM (SELECT * FROM mydb..Table_a) as a
INNER JOIN mydb..table_b
ON a.field1 = b.field2

Since the tempdb collation is different than mydb, it will generate an
error: “Cannot resolve collation conflict for equal to operation“, and
I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.

Any ideas how to change the tempdb collation? Any implications from
doing so? How will the master and msdb be affected? Do they also
need to change? What are the steps to change the tempdb to a new
collation?

> Your other option is to make sure that tempdb has the same location as
> your user database(s).

I assume you mean "collation" rather than "location", Tibor. I agree that
it's best to match the instance collation when possible. We have several
cases where the user database collation is different than the instance
collation (due to server consolidation) and and it's a real pain to manage.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:OJ11bzltIHA.6096@TK2MSFTNGP06.phx.gbl...
> You face the same situation as every international user (non-US) of SQL
> Server is facing.
>
>
> <<I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.>>
>
> Above *is* the reliable and responsible way to handle this.
>
> Your other option is to make sure that tempdb has the same location as
> your user database(s). If you don't control the installation (if the
> installation is done at some of your customer sites, for instance), then
> you need to be *very clear* about this in your installation manual.
>
>
> <<Any ideas how to change the tempdb collation?>>
>
> Rebuild the system databases or reinstall.
>
>
> <<Any implications from doing so?>>
>
> Everyting in the system databases will be lost.
>
>
> <<How will the master and msdb be affected? Do they also
> need to change? What are the steps to change the tempdb to a new
> collation?>>
>
> See above...
> You can't have a different collation for tempdb (which is drawn from
> model) than master/msdb. Such config is not supported.
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "shija03" <shija03@gmail.com> wrote in message
> news:b1d2ebd0-ff7c-4023-8a0a-f12bf32e2e38@27g2000hsf.googlegroups.com...
> My master and tempdb are in Latin collation, but all my other
> databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
> queries that require the usage of tempdb:
>
> SELECT a.*
> FROM (SELECT * FROM mydb..Table_a) as a
> INNER JOIN mydb..table_b
> ON a.field1 = b.field2
>
> Since the tempdb collation is different than mydb, it will generate an
> error: "Cannot resolve collation conflict for equal to operation", and
> I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.
>
> Any ideas how to change the tempdb collation? Any implications from
> doing so? How will the master and msdb be affected? Do they also
> need to change? What are the steps to change the tempdb to a new
> collation?
>
>

> I assume you mean "collation" rather than "location", Tibor.

Yep. Thanks for catching it.


> We have several cases where the user database collation is different than the instance collation
> (due to server consolidation) and and it's a real pain to manage.

Yes, I agree. No fun at all...

Sorry you didn't make it to the summit... :-(

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:6C6ACD90-9B8A-4278-BA2A-C69EAD1716D4@microsoft.com...
>> Your other option is to make sure that tempdb has the same location as your user database(s).
>
> I assume you mean "collation" rather than "location", Tibor. I agree that it's best to match the
> instance collation when possible. We have several cases where the user database collation is
> different than the instance collation (due to server consolidation) and and it's a real pain to
> manage.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:OJ11bzltIHA.6096@TK2MSFTNGP06.phx.gbl...
>> You face the same situation as every international user (non-US) of SQL Server is facing.
>>
>>
>> <<I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.>>
>>
>> Above *is* the reliable and responsible way to handle this.
>>
>> Your other option is to make sure that tempdb has the same location as your user database(s). If
>> you don't control the installation (if the installation is done at some of your customer sites,
>> for instance), then you need to be *very clear* about this in your installation manual.
>>
>>
>> <<Any ideas how to change the tempdb collation?>>
>>
>> Rebuild the system databases or reinstall.
>>
>>
>> <<Any implications from doing so?>>
>>
>> Everyting in the system databases will be lost.
>>
>>
>> <<How will the master and msdb be affected? Do they also
>> need to change? What are the steps to change the tempdb to a new
>> collation?>>
>>
>> See above...
>> You can't have a different collation for tempdb (which is drawn from model) than master/msdb.
>> Such config is not supported.
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "shija03" <shija03@gmail.com> wrote in message
>> news:b1d2ebd0-ff7c-4023-8a0a-f12bf32e2e38@27g2000hsf.googlegroups.com...
>> My master and tempdb are in Latin collation, but all my other
>> databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
>> queries that require the usage of tempdb:
>>
>> SELECT a.*
>> FROM (SELECT * FROM mydb..Table_a) as a
>> INNER JOIN mydb..table_b
>> ON a.field1 = b.field2
>>
>> Since the tempdb collation is different than mydb, it will generate an
>> error: "Cannot resolve collation conflict for equal to operation", and
>> I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.
>>
>> Any ideas how to change the tempdb collation? Any implications from
>> doing so? How will the master and msdb be affected? Do they also
>> need to change? What are the steps to change the tempdb to a new
>> collation?
>>
>>
>

> Sorry you didn't make it to the summit... :-(

Me too. I had planned to go to PASS instead (I can only take 1 trip this
year) but now it looks like I won't be able to make that either :-(

--
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:ewzEs7ntIHA.5268@TK2MSFTNGP06.phx.gbl...
>> I assume you mean "collation" rather than "location", Tibor.
>
> Yep. Thanks for catching it.
>
>
>> We have several cases where the user database collation is different than
>> the instance collation (due to server consolidation) and and it's a real
>> pain to manage.
>
> Yes, I agree. No fun at all...
>
> Sorry you didn't make it to the summit... :-(
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:6C6ACD90-9B8A-4278-BA2A-C69EAD1716D4@microsoft.com...
>>> Your other option is to make sure that tempdb has the same location as
>>> your user database(s).
>>
>> I assume you mean "collation" rather than "location", Tibor. I agree
>> that it's best to match the instance collation when possible. We have
>> several cases where the user database collation is different than the
>> instance collation (due to server consolidation) and and it's a real pain
>> to manage.
>>
>> --
>> Hope this s.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in message news:OJ11bzltIHA.6096@TK2MSFTNGP06.phx.gbl...
>>> You face the same situation as every international user (non-US) of SQL
>>> Server is facing.
>>>
>>>
>>> <<I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.>>
>>>
>>> Above *is* the reliable and responsible way to handle this.
>>>
>>> Your other option is to make sure that tempdb has the same location as
>>> your user database(s). If you don't control the installation (if the
>>> installation is done at some of your customer sites, for instance), then
>>> you need to be *very clear* about this in your installation manual.
>>>
>>>
>>> <<Any ideas how to change the tempdb collation?>>
>>>
>>> Rebuild the system databases or reinstall.
>>>
>>>
>>> <<Any implications from doing so?>>
>>>
>>> Everyting in the system databases will be lost.
>>>
>>>
>>> <<How will the master and msdb be affected? Do they also
>>> need to change? What are the steps to change the tempdb to a new
>>> collation?>>
>>>
>>> See above...
>>> You can't have a different collation for tempdb (which is drawn from
>>> model) than master/msdb. Such config is not supported.
>>>
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "shija03" <shija03@gmail.com> wrote in message
>>> news:b1d2ebd0-ff7c-4023-8a0a-f12bf32e2e38@27g2000hsf.googlegroups.com...
>>> My master and tempdb are in Latin collation, but all my other
>>> databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
>>> queries that require the usage of tempdb:
>>>
>>> SELECT a.*
>>> FROM (SELECT * FROM mydb..Table_a) as a
>>> INNER JOIN mydb..table_b
>>> ON a.field1 = b.field2
>>>
>>> Since the tempdb collation is different than mydb, it will generate an
>>> error: "Cannot resolve collation conflict for equal to operation", and
>>> I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.
>>>
>>> Any ideas how to change the tempdb collation? Any implications from
>>> doing so? How will the master and msdb be affected? Do they also
>>> need to change? What are the steps to change the tempdb to a new
>>> collation?
>>>
>>>
>>
>
>

Linchi's reply worked great! Basically this is all I had to do:

- Find a system at the same SQL Service Pack level than mine with a
Chinese_PRC_CI_AS collation model database.
- Backup the model database (both the Chinese_PRC_CI_AS and the local
SQL_Latin1_general_CP1_CI_AS)
- Restore the Chinese_PRC_CI_AS model database, in essence
orverwritting the SQL_Latin1_general_CP1_CI_AS one
- Stop MSSQL Server and restart.

BTW my SQL Server is 2000

Thank you

On May 14, 10:17pm, Linchi Shea
<LinchiS...@discussions.microsoft.com> wrote:
> See the following quote from this MS whitepaperhttp://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-...:
>
> <quote>
> Changing the tempdb Collation
>
> The collation of tempdb cannot be changed by using the ALTER DATABASE
> statement—SQL Server does not allow this since tempdb is part of the system
> database. Note that tempdb uses the collation of the model database. Since
> there is a way to change the collation of the model database, we inferred
> that we should be able to change the collation of tempdb. Recall that the
> model database can be backed up and restored. So, for example, if we have
> another instance of SQL Server running with a default collation of
> French_CI_AS, we can back up the model database from the “French” server,
> restore it on the target server, and then restart the MSSQL service on the
> target server. We used this technique to change the collation of tempdb for
> the test cases described in the previous section.
> </quote>
>
> Linchi
>
> "shija03" wrote:
> > My master and tempdb are in Latin collation, but all my other
> > databases are in Chinese_PRC_AS_CI, the problem comes when I do SQL
> > queries that require the usage of tempdb:
>
> > SELECT a.*
> > FROM (SELECT * FROM mydb..Table_a) as a
> > INNER JOIN mydb..table_b
> > ON a.field1 = b.field2
>
> > Since the tempdb collation is different than mydb, it will generate an
> > error: “Cannot resolve collation conflict for equal to operation“, and
> > I don't want to use "COLLATE DATABASE_DEFAULT" everywhere.
>
> > Any ideas how to change the tempdb collation? Any implications from
> > doing so? How will the master and msdb be affected? Do they also
> > need to change? What are the steps to change the tempdb to a new
> > collation?