Solved

How can I change collation on tempdb, master db without rebuild of other dbs?

Posted on 2007-11-30
2
2,452 Views
Last Modified: 2008-02-01
Hi all,
I have an instance of sql-2005.  All of the user db's were migrated from an sql-2000 instance, and they all have a collation of SQL_Latin1_General_CP1_CI_AS.

Yet the person who had previously set up this new instance had set it up with the collation Latin1_General_CI_AI.

Because of this I sometimes get collation errors, and from what I've read it's mostly due to sorting temp tables in the tempdb.  So is is possible to change the tempdb collation, or is that based on the master or model db's collation?  And if so, can I change either of those collations (without rebuilding all user dbs)?

Bottom line is I'm just looking for the easiest way to maintain the SQL_Latin1_General_CP1_CI_AS collation in my user db's and avoid the collation errors.  I have a terabyte or so of user db's and I want to avoid the downtime of backup/restore of those just to change a collation on the instance.  What's the best way to achieve this?
Thanks,
Jim
0
Comment
Question by:jimojimo
[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
2 Comments
 
LVL 31

Accepted Solution

by:
James Murrell earned 125 total points
ID: 20382698
this might help/point in right direction http://www.ssw.com.au/SSW/KB/KB.aspx?KBID=Q711843
0
 
LVL 1

Author Comment

by:jimojimo
ID: 20388378
cs97,
Thanks, I will try that and see if it works with the Tempdb.  All my user db's are in the 'correct' collation (that is, the collation I've chosen), it's just the server that has it's 'default' collation different from my user db collations.

 I'll try what you're link suggests--but without renaming/re-importing the user db's--just changing the default collation and restarting the server.  I would assume that since tempdb is recreated at server startup then it should be recreated with the new default collation, which I'll set to my 'correct' collation.

I'm still not sure if the Master db collation--which I assume will retain the 'in-correct' collation--will cause any of the error messages.  It's my understanding that the master shouldn't matter for sorting, temp tables, etc, but I'm wondering if anyone could shed light on that,
--Jim
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Incremental load example 2 56
Why is the output of this function is like this? 4 44
SQL Restore Script - Syntax Error 8 106
Getting max record but maybe not use Group BY 2 36
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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