Replace Text via SQL Replace in a DNN HTMLText Module

When migrating portals from one instance to another, you can use the template creation in DotNetNuke to export a template with content and then import that template to another instance. The problem you end up with however is the links tend to break as your portals directory usually has another id assigned to it.
With SQL 2005 you can use the replace function to replace some text, but you're dealing with an ntext field, and you also don't want to run the replace on all DesktopModules if you have a multi portal instance.

Here's a SQL 2005 stored procedure that will accept the text you want to find, what to replace it with, and restrict the search to a specific portal.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ReplaceDNNHTMLText]
@replace nvarchar(40),
@find nvarchar(40),
@PortalID int
As
update HTMLText Set DesktopHTML = cast(replace(cast(DesktopHTML as nvarchar(max)),@find,@replace) as ntext) 
from HTMLText WHERE ModuleID In (Select ModuleID from Modules WHERE PortalID = @PortalID)
AND DesktopHTML Like '%' + @find + '%'