Solved

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

Posted on 2011-09-09
10
1,161 Views
Last Modified: 2012-05-12
Sql 2005, tempdb collation is not the one we desired.
Can't afford to reinstall sql.
Any hints ?
0
Comment
Question by:gregajesih
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 7

Accepted Solution

by:
manu4u earned 500 total points
ID: 36508628
Heres more information about TempDb ... Just go through it .

http://msdn.microsoft.com/en-us/library/ms190768.aspx
0
 
LVL 5

Expert Comment

by:Maheshwar R
ID: 36508640
The Renaming the database,ropping the database cannot be performed on the tempdb database:
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36508642
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:hspoulsen
ID: 36509046
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
0
 

Author Comment

by:gregajesih
ID: 36509057
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.

G
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36509164
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,
Henrik
0
 

Author Comment

by:gregajesih
ID: 36510216
Okay, here is where it all begun:

create table #myprocname
as
  @var1=something

...
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      
as    
begin   
    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  
    begin   
         select @site2 = substring(@site, 0, 5)    
         if @site2<>'1000' and @site2<>'2000' and @site2<>'3000' and @site2<>'4000'      
         begin        
             set @error = @site2+' location doesnt exist.'       
             if @error<>''         
                 select @error as result     
         end  
    end          
end

Open in new window

0
 

Author Comment

by:gregajesih
ID: 36510228
..there is a syntax error added because we tried experimenting.. ignore that collation bind.
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36521058
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

?
0
 

Author Comment

by:gregajesih
ID: 36521064
hs,

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.

G

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

Thx, Bye
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question