Solved

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

Posted on 2011-09-09
10
1,139 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Script to Remove Data from Two Joined Tables 1 20
backup and restore 21 30
SSMS Opening Mode 9 20
Transact SQL - Frequency of Length of Distinct Values 3 26
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

837 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