Start Coding: Cannot resolve the collation conflict on temporary table?

"Cannot resolve the collation conflict between "Collation_A" and "Collation_B" in the equal to operation."

When I am using MSSQL 2000, it's normal when I hit this kind of collation problem on physically-existed table. Either the source database is conflict with new database, due to an upgrade from older version of customized database, or from other production site from different country.

But recently, I hit this issue at Temporary Tables (because I changed a new job and performed a new installation on my company laptop).

Let's say you have a following scripts written at stored procedure(SP):

-- Use AdventureWorks
create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
column1 varchar(200) NOT NULL)
INSERT INTO #tempTable
Values('test')
create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
column2 varchar(200) NOT NULL)
INSERT INTO tempTable
Values('test')
Select * from #tempTable t
inner join tempTable p
on t.ID = p.ID
where t.column1 = p.column2

--drop table #tempTable
--drop table tempTable

and when you execute it, you will get the following error message:

Msg 468, Level 16, State 9, Line 10
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

At the first look, I was confused what went wrong. ( Of course you can suggest assign the associate collation will do, but what if there's tons of SP you need to modify? And even worst, they are decrypted!)

Of course the best practice would be something like this:

CREATE TABLE #TestTempTab
(PrimaryKey int PRIMARY KEY,
Col1 nchar COLLATE database_default
)
as in Setting and Changing the Column Collation, but I doubt most people do that.

So first I traced down from the definition of the Temporary Tables. From MSDN:

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
...

The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix.

I queried from tempdb at System Databases with this query: (Note: you must use "Like" statement for the name)

use tempdb
Select collation, * from sysobjects so
inner join syscolumns sc
on so.id = sc.id
where so.name like '#tempTable%'

Why? Why it's collated in "Latin1_General_CI_AS"? Then I checked the database collation for this "tempdb" database and server instance:

Why all in "Latin1_General_CI_AS" collation? Then I went through the screenshot I took when I installed the MSSQL 2005 Developer Edition long long time ago (I have a habit to store the installation settings for future purpose, as in this case), I found this:

I switched the collation to Collation designator, which default to "Latin1_General_CI_AS" collation, as in my OS culture! I just realized I misused the in-appropriated collation with the current database (in this case "AdventureWorks", which uses "SQL_Latin1_General_CP1_CI_AS" collation.

So now, what is the solution?

I thought the faster way should be alter the "tempdb" database collation like this :

or from script:

USE [master]
GO
ALTER DATABASE [tempdb] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

But I get this error message:

I searched the MSDN/BOL, and I found this: Setting and Changing the Server Collation

So I inserted my installer in CD-ROM and tried to use this command:

start /wait setup.exe /qb INSTANCENAME={my SS2005 instance name} REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD={my SS2005 instance sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

but the wizard came and went but nothing happened: