Hi,
I just upgraded to 4.5.3 and it seems to have gone well with one exception. The database upgrade script was unable to create one of the indexes for the table dbo.Folders. The full details from 04.05.01.log are pasted below. I'm not too familiar with indexes in SQL Server. How significant is this error, and if it's significant, does anybody have any suggestions on how I can fix it?
System.Data.SqlClient.SqlException: CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Folders' and index name 'IX_FolderPath'. The duplicate key value is (0, GuidedTours/DotNetNuke/Modules/Announcements/).
Could not create constraint. See previous errors.
The statement has been terminated.
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 DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for dbo. and *****/
/***** *****/
/************************************************************/
/* add unique constraint to Folders table */
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'dbo.[IX_FolderPath]') and OBJECTPROPERTY(id, N'IsConstraint') = 1)
BEGIN
declare @PortalID int
declare @FolderPath varchar(300)
declare @FolderID int
declare @MinFolderID int
select @PortalID = min(PortalID)
from dbo.Portals
while @PortalID is not null
begin
/* check for duplicate FolderPaths */
select @FolderPath = null
select @FolderPath = FolderPath
from dbo.Folders
where PortalID = @PortalID
group by FolderPath
having COUNT(*) > 1
/* if duplicates exist */
if @FolderPath is not null
begin
/* iterate through the duplicates */
select @FolderID = min(FolderID)
from dbo.Folders
where PortalID = @PortalID
and FolderPath = @FolderPath
/* save min FolderID */
select @MinFolderID = @FolderID
while @FolderID is not null
begin
if @FolderID <> @MinFolderID
begin
/* reassign FolderId to min FolderID for duplicate folders */
update dbo.Files
set FolderID = @MinFolderID
where FolderID = @FolderID
/* remove duplicate folder */
delete
from dbo.Folders
where FolderID = @FolderID
end
select @FolderID = min(FolderID)
from dbo.Folders
where PortalID = @PortalID
and FolderPath = @FolderPath
and FolderID > @FolderID
end
end
select @PortalID = min(PortalID)
from dbo.Portals
where PortalID > @PortalID
end
/* add unique constraint */
ALTER TABLE dbo.Folders ADD CONSTRAINT
IX_FolderPath UNIQUE NONCLUSTERED
(
PortalID,
FolderPath
) ON [PRIMARY]
END
System.Data.SqlClient.SqlException: CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Files' and index name 'IX_FileName'. The duplicate key value is (179, admin.ascx).
Could not create constraint. See previous errors.
The statement has been terminated.
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 DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
/* add unique constraint to Files table */
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'dbo.[IX_FileName]') and OBJECTPROPERTY(id, N'IsConstraint') = 1)
BEGIN
declare @FolderID int
declare @FileName nvarchar(100)
declare @FileID int
declare @MinFileID int
select @FolderID = min(FolderID)
from dbo.Folders
while @FolderID is not null
begin
/* check for duplicate Filenames */
select @FileName = null
select @FileName = FileName
from dbo.Files
where FolderID = @FolderID
group by FileName
having COUNT(*) > 1
/* if duplicates exist */
if @FileName is not null
begin
/* iterate through the duplicates */
select @FileID = min(FileID)
from dbo.Files
where FolderID = @FolderID
and FileName = @FileName
/* save min FileID */
select @MinFileID = @FileID
while @FileID is not null
begin
if @FileID <> @MinFileID
begin
/* remove duplicate file */
delete
from dbo.Files
where FileID = @FileID
end
select @FileID = min(FileID)
from dbo.Files
where FolderID = @FolderID
and FileName = @FileName
and FileID > @FileID
end
end
select @FolderID = min(FolderID)
from dbo.Folders
where FolderID > @FolderID
end
ALTER TABLE dbo.Files ADD CONSTRAINT
IX_FileName UNIQUE NONCLUSTERED
(
FolderID,
FileName
) ON [PRIMARY]
END