Hi,
I might be teaching my grandmothers to suck eggs here  but I couldn't get the procedure to run properly - it just kept coming back with "There is an error in your query".
Googling around, I realised that - fairly obviously - if you have put an object qualifier on the database (as I had) when setting up your site, then you need to modify the code in 4 places (Assuming you want the procedure to reside in "your" part of the database) to add that qualifier as below.
After making these changes it seems to work OK - am I right here or am I making some big mistake through my ignorance??
CREATE Procedure YOURQUALIFIER_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 YOURQUALIFIER_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 YOURQUALIFIER_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
YOURQUALIFIER_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 |