INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Convert collation SQl 2012 system databases

Convert collation SQl 2012 system databases

(OP)
On SQL Server 2005 everything was SQL_Latin1_General_CP1_CI_AS. When I installed SQL 2012 Standard, I thought I had chosen the same collation but installed as Latin1_General_CI_AS. All of our databases that I migaated over (detach, attach, then change to 2012) are still SQL_Latin1_General_CP1_CI_AS so should keep them all sa there are many.

One application is having an issue becuase the database and system are different. Can I convert from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS relatively easy?

RE: Convert collation SQl 2012 system databases

Wouldnt it be easier to change the collation of the database?

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Convert collation SQl 2012 system databases

Collations are tricky.

There is a default collation for your server/instance. There is a default collation for each database, and there is a collation for each column of each table that is varchar or nvarchar (including the old text and ntext).

CODE

-- Server Collation
Select SERVERPROPERTY('Collation')

-- Database Collation
Select collation_name From sys.databases Where database_id = DB_ID()

-- Column Collation
Select Distinct collation_name From sys.columns where collation_name > '' 

Server collations are used when creating a new database. If you do not specify the collation for a new database, it will take the server's collation.

Database collations are used when creating new columns that are strings (char, nchar, varchar, nvarchar, text, ntext).

From my experience, the collation within a database is usually consistent. Meaning... the database default collation usually matches the column collations.

Unfortunately, changing all of this may not solve the real problem. The typical problem with collations usually involves temp tables and table variables. You see, when you create a temp table or table variable, it uses the collation of TempDB's default collation (unless you specify otherwise). The typical error is something like, "Cannot resolve collation conflict"

Mismatched collations are not easy to deal with.

I wrote a free SQL Server utility several years ago that can detect this problem. There are actually 2 checks. One checks for mismatched collations between the user database and tempdb. The other checks for mismatches between the default collation for the database and all of the string columns.

SQLCop is a free utility and can be downloaded here: http://sqlcop.lessthandot.com

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Convert collation SQl 2012 system databases

(OP)
I have googled that is why I posted on this site as what I've read seems a bit mundane for SQl 2012. I would think by now it would be relatively easier on master and tempdb. We have over 50 user databases so no it's definitely not easier to change the collation on those.

Is it possible to change the collation of just the tempdb and leave the master as is thus resolving any temp table issues?

RE: Convert collation SQl 2012 system databases

That would be nice, but....

No. You can't.

TempDB is dropped when the SQL Server Service is stopped and recreated when the service starts.

TempDB's collation is actually based on the default collation for the model database. According to this: http://technet.microsoft.com/en-us/library/ms18638... You cannot change the collation. (look at the restrictions part of that page).

Frederico's advice was spot on for changing the collation.

You say, "one application". This implies there are multiple applications, which also implies multiple databases. Would it be possible to install another instance of SQL on your server. Obviously, this instance would have the appropriate collation for that particular database. Then, detach it from your original instance and attach it to the new one. Finally, point the application to the new instance. I understand that this may not always be possible, but if it is, it is probably the easiest way to fix this problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Convert collation SQl 2012 system databases

(OP)
We have many applications with many databases that are all on the older collation. We already have 4 applications that are running on this sql server (system dbs new collation, databases old collation). I'm attempting a test upgrade of our accounting system (which will be bringing over 25 more databases) and that upgrade won't work becuase the collations are different.

I can't possible re-collate all these user databases. Since we're also using Snap Drives, I can't have 2 instances of SQL server running.

I need to get these system databases changed. I guess I need to make a phone call to Microsoft support else I'm screwed. There is documenation in the accounting upgrade that says the collations do not have to be the same. What a lie.

RE: Convert collation SQl 2012 system databases

(OP)
If I detach all our existing databases, do the reinstall, then reattach, will that not work? I can run a script to capture the sqllogins. We don't have many jobs running either, which I could script out.

RE: Convert collation SQl 2012 system databases

If you detach the databases, they will continue to have whatever their original default collation is, and the columns will continue to have their original collations also.

Seems like a detach, reinstall (with correct collation) and attach might be the appropriate way to go.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Convert collation SQl 2012 system databases

luvsql - Microsoft is going to tell you what I gave you on that link.
so ... detach all your db's that have the old collation - from what you said you should then be left with only the systems db's.

follow the steps on the link to reinstall the instance with the correct collation

re-attach your db's

and it should be it

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Convert collation SQl 2012 system databases

(OP)
We would also lose all of our sql logins. They (Microsoft) said the only way to do this was with a script that captures the sql logins but blanks out all the passwords when they're recreated. That would make all of our applications stop working.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close