Broken images and links within DotNetNuke are very common for instances of the application that have been migrated from one server to another, or from a server to a development machine. There are usually a few ways to combat the situation, but some ways cause more problems than they correct. This is because most solutions involve a quick Search and Replace within the data table by converting the value to a varchar, executing the REPLACE function and then updating the table with the new value. Often this causes trouble since varchar has a length maximum of 8000 characters, and many rows within your table may be longer than that, and will consequently end up truncated.
The problem is represented generally by images broken due to the URL within the text having a value which pushes the URL outside of the application domain. Meaning - you may have a server instance with image sources like: /Portals/0/thisimage.gif. On a server with the domain thisServer.com, while the local installation is contained within a virtual directory like “localhost/DNN_thisServer”. The images work on the server, but not within your local environment.
The reason for the failure is that the URL is reading the slash in the front of the URL and jumping to the parent directory, only since you are on a Virtual Directory on localhost - it is jumping outside of the application directory - therefore returning a broken link.
This is a very common problem, and the best solution is to replace the URL's on the fly with a corrected value. Since again we are restricted due to the data type of the column, we cannot simply exercise the REPLACE function within SQL to correct the values - instead we need to utilize the inherent text functions which are provided within SQL Server which takes a little more work, but does the trick.
Provided is a procedure which can be called to replace any value you want with whatever the replacement would be with the DesktopHtml column of the HtmlText table. To correct this issue - you can simply create this procedure on your database and execute it directly. For this above example you would execute:
Replace_DesktopHtml '/Portals/0/',''
Execution of the above would remove any instance of the /Portals/0/ value therefore correcting the issue. Verification has been added to the procedure to check that the anticipated length of the outgoing data matches the original length minus the count of values replaced and the difference between the value we are looking for and the value we are replacing it with. Any records which fail this verification are not updated and are instead returned by the procedure.
With no further ado - here is the full procedure:
CREATE Procedure Replace_DesktopHtml(
@For as varchar(100),
@With as varchar(100))
AS
--* REPLACES TEXT IN THE DESKTOP HTML MATCHING THE FOR VARIABLE
--* SAMPLE: Replace_DesktopHtml '/Portals/0/', ''
--* 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 moduleid
FROM HtmlText
WHERE PATINDEX('%'+@For+'%', DesktopHtml)>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
ModuleID,
DesktopHtml,
datalength(DesktopHtml),
DesktopHtml from HtmlText
where ModuleID = @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.DesktopHtml = t.[text]
FROM
HtmlText target JOIN #Temporary t
ON
target.ModuleId = 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