{"id":1842,"date":"2013-07-30T15:35:35","date_gmt":"2013-07-30T19:35:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1842"},"modified":"2013-07-30T15:44:32","modified_gmt":"2013-07-30T19:44:32","slug":"alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/","title":{"rendered":"ALTER DATABASE failed. The default collation of database &#8216;%.*ls&#8217; cannot be set to %.*ls."},"content":{"rendered":"<p>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.\u00a0 What started out as a straight forward upgrade, this actually became quite a challenge.\u00a0 After upgrading to SQL Server 2008R2 since SQL Server doesn\u2019t support direct upgrades from SQL Server 2000 to SQL Server 2012, I found <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-fixing-metadata-corruption-without-a-backup\/\" target=\"_blank\">metadata corruption<\/a>.\u00a0 We&#8217;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.<\/p>\n<p>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.\u00a0 When I got to the point of changing the database default collation I was dismayed to get the following error back from SQL Server:<\/p>\n<blockquote><p>Msg 1505, Level 16, State 1, Line 1<br \/>\nThe CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name &#8216;dbo.sysschobjs&#8217; and the index name &#8216;nc1&#8217;. The duplicate key value is (0, 1, person).<br \/>\nMsg 5072, Level 16, State 1, Line 1<br \/>\nALTER DATABASE failed. The default collation of database &#8216;TestCollationChange&#8217; cannot be set to SQL_Latin1_General_CP1_CI_AS.<\/p><\/blockquote>\n<p>Thinking about the previous metadata corruption, I was certain that there was something wrong with the database still, but I couldn&#8217;t find anything with CHECKDB or CHECKCATALOG.\u00a0 It turns out, there is nothing wrong with the database, there is something wrong with my expectations and assumptions.\u00a0 To demonstrate this, consider the following example:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE DATABASE &#x5B;TestCollationChange]\r\nON\u00a0 PRIMARY\r\n( NAME = N'TestCollationChange', FILENAME = N'C:\\SQLData\\TestCollationChange.mdf')\r\nLOG ON\r\n( NAME = N'TestCollationChange_log', FILENAME = N'C:\\SQLData\\TestCollationChange_log.ldf')\r\nCOLLATE Latin1_General_BIN;\r\nGO\r\nUSE &#x5B;TestCollationChange];\r\nGO\r\nCREATE TABLE dbo.Person\r\n(\u00a0\u00a0\u00a0 RowID int NOT NULL IDENTITY (1, 1),\r\nFirstName varchar(30) NOT NULL,\r\nLastName varchar(30) NOT NULL);\r\nGO\r\nALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (RowID);\r\nGO\r\nCREATE TABLE dbo.person\r\n(\u00a0\u00a0\u00a0 RowID int NOT NULL IDENTITY (1, 1),\r\nFirstName varchar(30) NOT NULL,\r\nLastName varchar(30) NOT NULL);\r\nGO\r\nALTER TABLE dbo.person ADD CONSTRAINT PK_person PRIMARY KEY CLUSTERED (RowID);\r\nGO\r\n<\/pre>\n<p>Under the Latin1_General_BIN collation, this is a completely valid schema because case sensitivity is applied.\u00a0 However, when you try and switch to a case insensitive collation with:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nALTER DATABASE &#x5B;TestCollationChange] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\nALTER DATABASE &#x5B;TestCollationChange] COLLATE SQL_Latin1_General_CP1_CI_AS;\r\n<\/pre>\n<p>these immediately become duplicate objects.\u00a0 So where do we go from here?\u00a0 First, the error message tells us that the object name is \u2018person\u2019, so you might consider doing a query against sys.objects:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT * FROM sys.objects where name = 'person';\r\n<\/pre>\n<p>The only problem is that this will return 1 row, remember we are still in Latin1_General_BIN so case sensitivity is being applied.\u00a0 To get around this, we need to change our query to collate the name column using our new collation:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT * FROM sys.objects where name COLLATE SQL_Latin1_General_CP1_CI_AS = 'person';\r\n<\/pre>\n<p>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:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE PROCEDURE &#x5B;TestProcedure] AS\r\nBEGIN\r\nSELECT \u2026\u2026.\r\n-- Lots more logic, etc\r\nEND\r\nGO\r\n\r\nCREATE PROCEDURE &#x5B;TESTProcedure] AS RETURN;\r\nGO\r\n<\/pre>\n<p>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&#8217;t personally ever build a database with duplicate object names regardless of the collation.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.\u00a0 What started out as a straight forward upgrade, this actually became quite a challenge.\u00a0 [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,48,38,39],"tags":[],"class_list":["post-1842","post","type-post","status-publish","format-standard","hentry","category-database-administration","category-sql-server","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>ALTER DATABASE failed. The default collation of database &#039;%.*ls&#039; cannot be set to %.*ls. - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ALTER DATABASE failed. The default collation of database &#039;%.*ls&#039; cannot be set to %.*ls. - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"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.\u00a0 What started out as a straight forward upgrade, this actually became quite a challenge.\u00a0 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-07-30T19:35:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-07-30T19:44:32+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"ALTER DATABASE failed. The default collation of database &#8216;%.*ls&#8217; cannot be set to %.*ls.\",\"datePublished\":\"2013-07-30T19:35:35+00:00\",\"dateModified\":\"2013-07-30T19:44:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/\"},\"wordCount\":705,\"commentCount\":2,\"articleSection\":[\"Database Administration\",\"SQL Server\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/\",\"name\":\"ALTER DATABASE failed. The default collation of database '%.*ls' cannot be set to %.*ls. - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2013-07-30T19:35:35+00:00\",\"dateModified\":\"2013-07-30T19:44:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Administration\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/database-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"ALTER DATABASE failed. The default collation of database &#8216;%.*ls&#8217; cannot be set to %.*ls.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"ALTER DATABASE failed. The default collation of database '%.*ls' cannot be set to %.*ls. - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/","og_locale":"en_US","og_type":"article","og_title":"ALTER DATABASE failed. The default collation of database '%.*ls' cannot be set to %.*ls. - Jonathan Kehayias","og_description":"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.\u00a0 What started out as a straight forward upgrade, this actually became quite a challenge.\u00a0 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-07-30T19:35:35+00:00","article_modified_time":"2013-07-30T19:44:32+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"ALTER DATABASE failed. The default collation of database &#8216;%.*ls&#8217; cannot be set to %.*ls.","datePublished":"2013-07-30T19:35:35+00:00","dateModified":"2013-07-30T19:44:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/"},"wordCount":705,"commentCount":2,"articleSection":["Database Administration","SQL Server","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/","name":"ALTER DATABASE failed. The default collation of database '%.*ls' cannot be set to %.*ls. - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2013-07-30T19:35:35+00:00","dateModified":"2013-07-30T19:44:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Database Administration","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/database-administration\/"},{"@type":"ListItem","position":3,"name":"ALTER DATABASE failed. The default collation of database &#8216;%.*ls&#8217; cannot be set to %.*ls."}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1842","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1842"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1842\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1842"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1842"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}