Fixing Collation: The Experiments

We’re trying to solve a few collation issues we have been having, and I had collation overload this week.

The Scenario
•    Some of our SQL Server instanced were installed using the default collation SQL_Latin1_General_CP1_CI_AS
•    We need to have case insensitive, accent insensitive – Latin1_General_CI_AI
•    We changed some of our database collations to Latin1_General_CI_AI

The Problems

•    We want to change the model database’s collation, so that all new databases get the collation we want
•    We want to change the tempdb collation, so that all temporary objects don’t have to use explicit collation

We use temporary tables in some of our important stored procedures and UDFs. It will be an issue if we have different collations between our user databases and tempdb. Yup, trying to do a query that involved these two tables will result in an error similar to:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Background Check

There are many people who have blogged about how to go around this issue.

Kimberly Tripp had a article on Changing Database Collation and dealing with TempDB Objects.

Kimberly basically suggests to create the temp table objects while inside the user database, and to explicitly specify a database_default collation on every column, as such (code snippet adaptation from her example):

   1: USE userdb
   2: GO
   3:
   4: CREATE TABLE #mytemptable
   5: (
   6:    col1 VARCHAR(100) COLLATE database_default
   7: )

This forces the tempdb object to use whatever collation you have specified for your user database.

Michelle Guzait also has a very good article on collation fixes.  In this article she provides:

  • Sample collation conflict error messages
  • Tips on how to resolve collation problems
  • Script to find problematic databases
  • Script to change temporary table collation

However in our case, we wanted to avoid having to create all our temp objects like this – if we can help it. So I tried a few other things to force changing tempdb collation.

The Experiments

Tempdb gets recreated every time the SQL Server service is restarted, and tempdb gets its default properties from the model database.

So if we can change the model database’s collation, problem solved right?

Changing a system database’s collation is not an easy ALTER DATABASE statement.

These are the two experiments I tried:

Experiment 1: Attach model db with correct collation (in our case Latin1_General_CI_AI)

1. Installed a new instance of SQL Server (SQLCIAI) with the same Service Packs, and the collation we wanted

2. Stop SQLCIAI, and make a copy of the model mdf and ldf file

•    modelciai.mdf
•    modelciai.ldf

3. Stop the existing instance (SQL02)

4. Restart existing instance in single user mode by passing the following startup options (http://support.microsoft.com/kb/224071)

-c;-m;-T3608;

-c shortens startup time when starting SQL Server from the command prompt
-m starts an instance of SQL Server in single-user mode
-T3608 prevents SQL Server from automatically starting and recovering any database except the master database

5. Detach model db, and attach new model db

   1: USE master
   2: GO
   3:
   4: EXEC sp_detach_db 'model'
   5: GO
   6:
   7: -- show files
   8: SELECT *
   9: FROM sys.sysfiles
  10:
  11: EXEC sp_attach_db 'model',
  12: 'C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLDATAmodelciai.mdf',
  13: 'C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLDATAmodelciai.ldf'
  14: GO

6. Start up in multi user mode

7. Test

Experiment 1 Result: Seemed successful at first. Model db and tempdb get the collation we want. But when I go to Management Studio and right click to see database properties, I get the following error:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Collation Issue

Collation Issue


Experiment 2: Restore model db with correct collation (in our case Latin1_General_CI_AI)

1. Installed a new instance of SQL Server (SQLCIAI) with the same Service Packs, and the collation we wanted

2. Backup model db of SQLCIAI

3. Restore to existing instance

   1: -- this file has ciai (case insensitive, accent insensitive)
   2: RESTORE DATABASE [model]
   3: FROM  DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.5MSSQLBackupmodelciai.bak'
   4: WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
   5: GO

4. Restart service

Experiment 2 Results: same as Experiment 1

Conclusion

Although I spent some time on these without much success, I think these were still good experiments. At least we know what “seems” to work, and what really works. But at this point we have conceded to the fact we need to set aside some time to rebuild our servers (see MSDN:  Setting and Changing the Server Collation – http://msdn.microsoft.com/en-us/library/ms179254.aspx.). This should be the best way to change the collation issue.

References

Kimberly Tripp. Changing Database Collation and dealing with TempDB Objects.

http://www.sqlskills.com/blogs/Kimberly/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx.

Michelle Guzait. SQL Server collation conflict fixes.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1186718,00.html

MSDN. How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server.

http://support.microsoft.com/kb/224071

MSDN. Setting and Changing the Server Collation

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

MSDN. Setting and Changing the Database Collation

http://msdn.microsoft.com/en-us/library/ms175835.aspx

MSDN. Setting and Changing the Column Collation

http://msdn.microsoft.com/en-us/library/ms190920.aspx

MSDN. SQL Server Backup Options

http://msdn.microsoft.com/en-us/library/ms190737.aspx

MSDN. SQL Server Trace Flags

http://msdn.microsoft.com/en-us/library/ms188396.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s