SQL Server – Error – “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.sysowners’ and the index name ‘nc1’. The duplicate key value”

 

Background

Trying to add Reporting Services to an existing SQL Server v2014 Install this afternoon, and finally ran into a problem that is worthy of talking about.

 

Steps

We are on MS SQL Server 2014 Standard Edition

  1. Added Reporting Services to an existing install
  2. And, now going through the Change Database Steps
    • Specifically, the Progress and Finish Step
    • While processing the SQL Code within the “Running database script” step, we ran into an Error

 

reportdatabasedatabaseconfigured-progressandfinish-20170130-0140pm

 

 

Error

Here is the error message

Error Image

exceptiondetails

Error Text

 


System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.sysowners' and the index name 'nc1'. The duplicate key value is (spotlight).
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
   at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
   at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)

 

TroubleShooting

SQL Server Profiler

Ran SQL Server Profiler and captured the error pasted below:

Trace

Image

sqlserverprofiler-20170130-0154pm-brushed-up

 

Textual

The create unique Index Statement terminated because a duplicate key was found for the object ‘dbo.sysowners’ and the index named ‘nc1’.  
The duplicate key value is (spotlight).

 

msdb

Knowing that msdb is the foundational database took a look at it.

sysusers

Code


use [msdb]
go

select *

from   sysusers tblSU

--where  lower(tblSU.[name]) like '%spot%'

order by 
		lower(tblSU.[name])


 

Output

sys-sysusers-20170130-0321pm

 

Explanation

  1. In the msdb database
    • We have two distinct users Spotlight and spotlight
    • This is possible as out server collation

 

Validate Server Collation

Code


print 'Collation: ' + cast( serverproperty('collation') as varchar(60))

Output

servercollation-20170130-0324pm

 

Validate Database ( msdb ) Collation

Code


print 'Collation - SQL Instance: ' + cast( serverproperty('collation') as varchar(60))

print 'Collation - Database - msdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('msdb','collation'));  

print 'Collation - Database - ReportServer - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServer','collation'));  

print 'Collation - Database - ReportServerTempdb - ' + CONVERT (varchar, DATABASEPROPERTYEX('ReportServerTempDB','collation'));  


Output

collation-view-20170130-0330pm

 

Explanation

  1. On our embryonic database, msdb, it is OK to have users whose name only vary based on case
    • BTW, for this server whose collation is binary, case materializes
  2. But, on new to become database which is case-insensitive, it is not OK

 

 

Listening

To me it is a cute problem, but don’t ask my lover

As, she is the only one who makes herself pretty for me

Bellamy Brothers
Let Your Love Flow
Link

Microsoft Connect

Opened up a Connect Item

  1. Title :- Reporting Services – Configuration – Index Creation error on table dbo.sysowners – The index name nc1
    ID :- 3120021
    Date Created :- 2107-Jan-30th
    Status :- Active
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

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