ALTER DATABASE failed. The default collation of database ‘%.*ls’ cannot be set to %.*ls.

Last week I was working with a client on upgrading one of their systems from SQL Server 2000 to SQL Server 2012, while also performing a collation change of the database and all of the table columns from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS.  What started out as a straight forward upgrade, this actually became quite a challenge.  After upgrading to SQL Server 2008R2 since SQL Server doesn’t support direct upgrades from SQL Server 2000 to SQL Server 2012, I found metadata corruption.  We’ve seen and dealt with this before, so back to SQL Server 2000 to fix the orphaned entries, and then another upgrade attempt to SQL Server 2008R2.

At this point I had a corruption free database and started running the scripts I had generated to migrate from Latin1_General_BIN to SQL_Latin1_General_CP1_CI_AS.  When I got to the point of changing the database default collation I was dismayed to get the following error back from SQL Server:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.sysschobjs’ and the index name ‘nc1’. The duplicate key value is (0, 1, person).
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database ‘TestCollationChange’ cannot be set to SQL_Latin1_General_CP1_CI_AS.

Thinking about the previous metadata corruption, I was certain that there was something wrong with the database still, but I couldn’t find anything with CHECKDB or CHECKCATALOG.  It turns out, there is nothing wrong with the database, there is something wrong with my expectations and assumptions.  To demonstrate this, consider the following example:

CREATE DATABASE [TestCollationChange]
ON  PRIMARY
( NAME = N'TestCollationChange', FILENAME = N'C:\SQLData\TestCollationChange.mdf')
LOG ON
( NAME = N'TestCollationChange_log', FILENAME = N'C:\SQLData\TestCollationChange_log.ldf')
COLLATE Latin1_General_BIN;
GO
USE [TestCollationChange];
GO
CREATE TABLE dbo.Person
(    RowID int NOT NULL IDENTITY (1, 1),
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL);
GO
ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (RowID);
GO
CREATE TABLE dbo.person
(    RowID int NOT NULL IDENTITY (1, 1),
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL);
GO
ALTER TABLE dbo.person ADD CONSTRAINT PK_person PRIMARY KEY CLUSTERED (RowID);
GO

Under the Latin1_General_BIN collation, this is a completely valid schema because case sensitivity is applied.  However, when you try and switch to a case insensitive collation with:

ALTER DATABASE [TestCollationChange] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [TestCollationChange] COLLATE SQL_Latin1_General_CP1_CI_AS;

these immediately become duplicate objects.  So where do we go from here?  First, the error message tells us that the object name is ‘person’, so you might consider doing a query against sys.objects:

SELECT * FROM sys.objects where name = 'person';

The only problem is that this will return 1 row, remember we are still in Latin1_General_BIN so case sensitivity is being applied.  To get around this, we need to change our query to collate the name column using our new collation:

SELECT * FROM sys.objects where name COLLATE SQL_Latin1_General_CP1_CI_AS = 'person';

This will show us both of the objects and it becomes immediately clear why we have a duplication issue, different cases.In the case of the actual database I was working on, the duplicate objects were two stored procedures (actually four if you think about), and the duplicates had object definitions similar to the following:

CREATE PROCEDURE [TestProcedure] AS
BEGIN
SELECT …….
-- Lots more logic, etc
END
GO

CREATE PROCEDURE [TESTProcedure] AS RETURN;
GO

I have no idea what the intent of the second procedure was, but after consulting with the client, it was determined that these duplicate stubs could be dropped, which then allowed the database collation change to SQL_Latin1_General_CP1_CI_AS. This might not be a viable solution if the application actually relies on the case sensitive nature of the naming convention, though I wouldn’t personally ever build a database with duplicate object names regardless of the collation.

Updated Availability Group Demonstrator

Since the first release of the SQLskills Availability Group Demonstrator, I’ve had an number of requests to add the ability to enable MultiSubnetFailover in the connection string, and to specify a Timeout value. I made these changes months ago but never got around to actually releasing the newer build on my blog. However, after releasing the two add-ins yesterday, I took a few minutes and ran through testing the demonstrator application against my local Availability Group and then uploaded the latest build for download.

Availability Group Demonstrator

SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances

Extended Events are a powerful new way of troubleshooting problems with SQL Server, and the addition of UI support in SQL Server 2012 Management Studio has helped increase the awareness and usage of this feature. One short-coming is that the new UI for Extended Events only works for SQL Server 2012 instances leaving administrators that manage multiple versions, no way to explore the option of using Extended Events on their SQL Server 2008/R2 instances.  The Extended Event Manager Add-in that I wrote for SQL Server 2008/R2 is not compatible with SQL Server 2012 and because of the new UI I never planned on making it function in Management Studio 2012.  However, I’ve had a lot of requests for this to provide backwards compatibility, and I’ve been reminded by Erin every time she presents on Extended Events about how useful it would be if I would make the Add-in work in Management Studio 2012.

Today, we’re releasing a new SQL Server 2012 Extended Events Add-in  to provide backwards compatibility with SQL Server 2008 and SQL Server 2008R2 for Extended Events by providing the following features:

  • View Extended Events Metadata for all Available Objects
  • View event sessions
  • Start/Stop event sessions
  • Create new event sessions
  • Alter event sessions
  • Drop event session
  • Script all operations
  • View target data for active event sessions
  • Configurable UI options

The SQL Server 2012 Extended Events Add-in is built on the code for the 2008 version of the add-in (available on Codeplex) with updates to resolve known bugs and allow integration in SQL Server 2012 Management Studio. A full walkthrough of the UIs in the add-in can be found on my blog post An XEvent a Day (12 of 31) – Using the Extended Events SSMS Addin.

Many thanks go out to Dan Taylor (@DBABullDog) and Paul Timmerman (@mnDBA) for beta testing the add-in over the last week, providing feedback and bug reports that were critical to getting the add-in modified for SQL Server 2012.  Also thanks to Erin (@erinstellato) for the constant reminders that this would be a useful feature.