We had a DNN 4.4.1 to 4.5.1 upgrade that threw a few SQLDataProvider errors during the installer that were caused by duplicate files in DNN Files table. Not sure how they got there, but here is a SQL statement to check if they exist:
SELECT
PortalID, Filename, Folder, COUNT(Filename) AS NumOccurrences
FROM
files
GROUP
BY Filename, PortalID, Folder
HAVING
( COUNT(Filename) > 1 AND Count(PortalID) > 1 AND Count(Folder) > 1)
Here is the SQL that was provided in the log file from the installer:
/* 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 Folders
while @FolderID is not null
begin
/* check for duplicate Filenames */
select @FileName = null
select @FileName = FileName
from 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 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 Files
where FileID = @FileID
end
select @FileID = min(FileID)
from Files
where FolderID = @FolderID
and FileName = @FileName
and FileID > @FileID
end
end
select @FolderID = min(FolderID)
from Folders
where FolderID > @FolderID
end
ALTER TABLE dbo.Files ADD CONSTRAINT
IX_FileName UNIQUE NONCLUSTERED
(
FolderID,
FileName
) ON [PRIMARY]
END