Cannot alter the database 'tempdb' because it is a system database

Sql 2005, tempdb collation is not the one we desired.
Can't afford to reinstall sql.
Any hints ?
Who is Participating?
manu4uConnect With a Mentor Commented:
Heres more information about TempDb ... Just go through it .
Maheshwar RSoftware DeveloperCommented:
The Renaming the database,ropping the database cannot be performed on the tempdb database:
You cannot change the collation of tempdb. As per the error message it is a system database. Even if you did change it collation it wouldnt matter as it would be recreated from the model db on the next reboot anyway.

You cannot change the collation of model either.

Im affraid you have to re-install.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

as a temporary fix, you can write COLLATE DATABASE_DEFAULT
on your join clauses with tables from TempDb.

There are several links on how to change the collation of your user databases, but I do not think that you can change TempDB unless you reinstall.

Best regards,
Henrik Staun Poulsen, Stovi Software
gregajesihAuthor Commented:
Henrik, thanks.

But this is the exact thing we wanted not-to do.
Write additional explicit collation translation code in order to accomodate the original lack of aligned collations in databases.

Someone's sloppyness is other one's problem.

Hi G,

Well, there are two parties at fault here.
One installing SQL Server with an unexpected collation, and then the original developer making assumptions about TempDB collation.

It reminds me of people relying on a particular date format, as in mm/dd/yyyy or the entire Y2K problem.

If you are the developer, you'll find that it does not take too long to find the entire code base (from version control!) and search for all temp tables (tables starting with a #) . If a temp table contains a character field, then write COLLATE DATABASE_DEFAULT on creating the table, or on all join clauses.
Then things should start working again.

Best regards,
gregajesihAuthor Commented:
Okay, here is where it all begun:

create table #myprocname

If I removed # from myprocname to allow local creation, it was ok.
If I did not do that, the error happened.

Entire code attached.

create procedure #validate_pricechange_form  @user varchar(100), @Date datetime, @Site varchar(100), @No_change int      
    declare @site_txt varchar(100)   
    set @site_txt= @site  
    select @site_txt = substring(@site_txt, 8, len(@site_txt))  
    select @site_txt = rtrim(@site_txt) 
    select @site_txt = ltrim(@site_txt)  
    declare @site2 char(4) collateion Latin1_General_CI_AI
    select @site2=cast([site] as varchar) from master.location where naziv=@site_txt  
    declare @valid int           
    declare @error nvarchar(4000)     
    set @error = ''     
    if @site2 is NULL  
         select @site2 = substring(@site, 0, 5)    
         if @site2<>'1000' and @site2<>'2000' and @site2<>'3000' and @site2<>'4000'      
             set @error = @site2+' location doesnt exist.'       
             if @error<>''         
                 select @error as result     

Open in new window

gregajesihAuthor Commented:
..there is a syntax error added because we tried experimenting.. ignore that collation bind.
Hi G,

I did not know that you could create stored procedures starting with "#". So I've learnt something new today. Thank you.

And you do a SUBSTRING with position 0 (zero), How does that work?
Ahh, it means take characters from position 0 to position 4, 4 characters in total.

What happens if you do:

select @site2=cast([site] as varchar) from master.location where naziv=@site_txt  COLLATION DATABASE_DEFAULT

gregajesihAuthor Commented:

id doesn't matter.
I don't know what happens because i refuse to do this kind of coding with explicit collation transformations.
Our final decision was to omit using create #myproc as a workaround.


I should have earned some points, too. 1 for example :-)

Thx, Bye
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.