After migrating to Active Forums 4.1 I decided to uninstall the old 3.7 version but did not realize in many postings I would have references to emoticons from folders that I deleted by uninstalling AF 3.7. First I wanted to have a look what was the problem in those postings. To see such a posting I ran:
SELECT * FROM dbo.activeforums_Content
WHERE Body LIKE '%desktopmodules/ntforums/images/emoticons%'
That will give you a list with all postings that contain links to emoticons that are no longer available in the folder 'desktopmodules/ntforums/images'.
You can change by using this procedure:
USE [(databasename)]
GO
/****** Object: StoredProcedure [dbo].[Activeforums_Content_Replace_Body] Script Date: 11/18/2009 20:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Activeforums_Content_Replace_Body](
@For as varchar(100),
@With as varchar(100))
AS
--* REPLACES TEXT IN THE DESKTOP HTML MATCHING THE FOR VARIABLE
--* SAMPLE: Activeforums_Content_Replace_Body 'Old_Text', 'New_text'
--* MAIN DECLARATION
DECLARE
@pointer binary(16),
@index INT,
@lenFor INT,
@lenWith INT,
@diff int,
@id INT,
@count INT
--* STANDARD RUNTIME VARIABLES
SET @lenFor = LEN(@For)
SET @diff = @lenFor - LEN(@With)
--* CREATE THE TEMPORARY TABLE
CREATE TABLE #Temporary
(
[id] int, --MAPS TO YOUR SOURCE TABLE PKID
[oldtext] text, --ORIGINAL SOURCE TEXT (NOT REQUIRED)
[oldlength] int,--ORIGINAL SOURCE LENGTH
[text] text, --NEW TEXT VALUE
[length] int, --NEW LENGTH
[cLength] int --CHECKSUM LENGTH
)
--* LOOP THROUGH THE SOURCE TABLE
--* IDENTIFY ALL RECORDS WHICH MATCH YOUR PATTERN
DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT ContentID
FROM Activeforums_Content
WHERE PATINDEX('%'+@For+'%', Body)>0
OPEN irows
FETCH NEXT FROM irows INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
--* INSERT MATCHING RECORDS INTO THE TEMPORARY TABLE
INSERT INTO #Temporary(id, oldtext, oldlength, text)
SELECT
ContentID,
Body,
datalength(Body),
Body from activeforums_Content
where ContentID = @id
--* GRAB THE POINTER OF THE OBJECT TO BE USED FOR UPDATETEXT
SELECT
@pointer = TEXTPTR(text)
FROM #Temporary
WHERE id=@id
--* GET THE FIRST INDEX OF OUR PATTERN
SELECT
@index = PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id
--* IF WE FOUND ANY ENTRIES, LOOP UNTIL WE REPLACE ALL OF THEM
IF @index > 0
BEGIN
select @count = 0
WHILE (
SELECT
PATINDEX('%'+@For+'%', text)
FROM #Temporary
WHERE id=@id
) > 0
BEGIN
--* KEEP A RUNNING TOTAL OF IDENTIFIED ENTRIES FOR LENGTH CHECKSUM
select @count = @count + 1
SELECT
@index = PATINDEX('%'+@For+'%', text)-1
FROM #Temporary
WHERE id=@id
--* UPDATE THE TEMPORARY VALUE
UPDATETEXT #Temporary.text @pointer @index @lenFor @With
END
--* UPDATE THE TEMPORARY RECORD, SET LENGTH AND CLENGTH VALUES FOR CHECKSUM
UPDATE #Temporary set
length=datalength(text),
cLength=datalength(oldtext) - @count * @diff
WHERE id=@id
END
FETCH NEXT FROM irows INTO @id
END
CLOSE irows
DEALLOCATE irows
--* UPDATE THE DATABASE FOR ALL RECORDS IN THE TEMPORARY
--* WHERE THE CHECKSUM LENGTH MATCHED THE RESULT LENGTH
UPDATE target set
target.Body = t.[text]
FROM
activeforums_Content target JOIN #Temporary t
ON
target.ContentID = t.id AND
t.length = t.cLength
--* DELETE ALL RECORDS FROM TEMPORARY WHICH WERE SUCCESSFUL
DELETE from #Temporary where length=clength
--* RETURN ALL ROWS WHICH FAILED LENGTH COMPARISON
--* THIS SHOULD ALWAYS BE EMPTY
select * from #Temporary
--* DROP THE TEMP TABLE AND EXIT
DROP TABLE #Temporary
The above will create a stored procedure. After that run:
<code>
Activeforums_Content_Replace_Body 'desktopmodules/ntforums/images/emoticons', 'placeholder'
Activeforums_Content_Replace_Body 'placeholder', 'desktopModules/activeForums/themes/_default/emoticons'
</code>
Use at your own risk...