Convert collation SQl 2012 system databases

[Luvsql]

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?

 

[fredericofonseca]

quite easy to google for the answer --

http://technet.microsoft.com/en-us/library/ms179254.aspx

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SQLScholar]

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

----------------------------------------

http://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
----------------------------------------

 

[gmmastros]

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).

-- 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

 

[Luvsql]

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?

 

[gmmastros]

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/ms186388.aspx

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

 

[Luvsql]

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.

 

[Luvsql]

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.

 

[gmmastros]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Luvsql]

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.