I got tired of seeing this error myself. A little investigation into the database identified the root cause as the stored proc that adds items to the SearchItem table. As installed by DNN distribution modules, it does not check to see if a record already exists for the ModuleID and SearchKey that it is trying to add. Since there is a unique index on the table for these fields, when the stored proc tries to insert a duplicate, the error gets thrown. The fix is to check to see if the record already exists, and if it does, just update it instead of trying to insert it. In both cases, the ID of the record needs to be returned. Here is the code I used to do an alter on the stored proc:
SET Ansi_Nulls ON
GO
SET Quoted_IdentIfier ON
GO
ALTER PROCEDURE [dbo].[AddSearchItem]
@Title NVARCHAR(200),
@Description NVARCHAR(2000),
@Author INT,
@PubDate DATETIME,
@ModuleId INT,
@SearchKey NVARCHAR(100),
@Guid NVARCHAR(200),
@ImageFileId INT
AS
DECLARE @ID INT
SELECT @ID = SearchItemId
FROM SearchItem
WHERE ModuleId = @ModuleID
AND SearchKey = @SearchKey
IF @ID IS NULL
BEGIN
INSERT INTO SearchItem
([Title],
[Description],
[Author],
[PubDate],
[ModuleId],
[SearchKey],
[guId],
[HitCount],
[ImageFileId])
VALUES (@Title,
@Description,
@Author,
@PubDate,
@ModuleId,
@SearchKey,
@Guid,
0,
@ImageFileId)
SELECT Scope_identity()
END
ELSE
BEGIN
UPDATE [SearchItem]
SET [Title] = @Title,
[Description] = @Description,
[Author] = @Author,
[PubDate] = @PubDate,
[ModuleId] = @ModuleId,
[SearchKey] = @SearchKey,
[guId] = @Guid,
[HitCount] = [HitCount] + 1,
[ImageFileId] = @ImageFileId
WHERE SearchItemId = @ID
SELECT @ID
END
I also found a smaller error in a related stored proc that I am not sure is causing any problems, but should probably be fixed as well. Here is the code to fix this error, which just adds a BEGIN - END block around the ELSE portion of the code:
SET Ansi_Nulls ON
GO
SET Quoted_IdentIfier ON
GO
ALTER PROCEDURE [dbo].[AddSearchItemWord]
@SearchItemID INT,
@SearchWordsID INT,
@Occurrences INT
AS
DECLARE @ID INT
SELECT @id = SearchItemWordId
FROM SearchItemWord
WHERE SearchItemId = @SearchItemID
AND SearchWordsId = @SearchWordsID
IF @ID IS NULL
BEGIN
INSERT INTO SearchItemWord
([SearchItemId],
[SearchWordsId],
[Occurrences])
VALUES (@SearchItemID,
@SearchWordsID,
@Occurrences)
SELECT Scope_identity()
END
ELSE
BEGIN
UPDATE SearchItemWord
SET Occurrences = @Occurrences
WHERE SearchItemWordId = @id
AND Occurrences <> @Occurrences
SELECT @id
END
You can implement both of these changes by copying each of the code sections above into the SQL code execution tool within the Host menu. After I put these in place, the errors stopped completely.