Solved

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

Posted on 2011-09-09
10
1,099 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 4

Expert Comment

by:Mahesh Reddy
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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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 …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now