ALUI Tool: URL (or text) Migration within Publisher Items

Following my previous article "ALUI Administration Tool for Environment Refresh: String Replacing for URLS" talking about migration between environments, here is an extra piece that you might find very useful (I surely use it all the time)

Basically, as explained in the previous article, it is common to have different DNS aliases set up per environment...I.e for publisher remote server, you could have:

• http://publisher.domain.com for production
• http://publisher-stg.domain.com for staging
• http://publisher-dev.domain.com for development

Similarly, the publish browsing URL is not an exception to this rule:

http://publisher-content.domain.com/publish  for production

http://publisher-content-stg.domain.com/publish for staging

http://publisher-content-dev.domain.com/publish for development

When you add an image or a link in the free text editor of content items in publisher, it will most of the time create an absolute URL to that resource...thus you can imagine that there will be a lot of DNS aliases within a lot of publisher items throughout the environment.

What happen when you migrate the publisher DB from one environment to another? Well you will have a lot of DEV dns aliases within your Staging environment (in case of a DEV promotion to Stage); or a lot of production DNS aliases within your dev environment in the case of a production refresh to DEV.

In my previous article "ALUI Administration Tool for Environment Refresh: String Replacing for URLS", I was mostly talking about migrating URL within portal objects, but nothing really about migrating urls within publisher items.

Thus, I created some DB scripts (SQL Server only for now) that do just that...

1. puburls-PTCSDIRECTORY-nvarchar-replace.sql: Script to change a particular string within the PUBLISHEDTRANSFERURL and PUBLISHEDURL columns (which is mapped out in the DB to a column of type VARCHAR)
2. puburls-PTCSVALUE-ntext-replace.sql and puburls-PTCSVALUEA-ntext-replace.sql: Scripts to change a particular string within the "long text" property of a publisher item (which is mapped out in the DB to a column of type TEXT)
1. PCSVALUES.LONGVALUE (hosting the long text of the currently published item)
2. PCSVALUESA.LONGVALUE (hosting the long text values of all the previous versions of the item)

For the first script, PUBLISHEDTRANSFERURL and PUBLISHEDURL columns are of type VARCHAR and thus it is easy to replace a string within those columns using the REPLACE MS SQL Function. Thus, a simple SQL statement is good here.

The main challenge was really with the 2nd scripts...indeed, within a column of type TEXT, the SQL "REPLACE" function cannot be used...The workaround is to use the PATINDEX and UPDATETEXT functions within a Transact-SQL (T-SQL) script. To give the credit to to the right person, I adapted a script that I found at ASP FAQ - How do I handle REPLACE() within an NTEXT column in SQL Server?

DISCLAIMER: ALTHOUGH I PERSONNALY USE THIS SCRIPT ALL THE TIME, THERE IS NO GUARANTY; SO USE THIS TOOL AT YOUR OWN RISK blah blah blah AND USE IT ONLY IF YOU ARE PROFFICIENT ENOUGH WITH ALUI PORTAL TECHNOLOGIES.

Attached is the zip file package that contains the 3 scripts:

• puburls-PTCSDIRECTORY-nvarchar-replace.sql
• puburls-PTCSVALUE-ntext-replace.sql
• puburls-PTCSVALUEA-ntext-replace.sql

Don't forget to change the string to look for, and the string to replace it with

puburls-PTCSDIRECTORY-nvarchar-replace.sql

UPDATE [dbo].[PCSDIRECTORY]
SET 
[PUBLISHEDTRANSFERURL]=REPLACE([PUBLISHEDTRANSFERURL],'-DEV.DOMAIN.COM','-TST.DOMAIN.COM'), 
[PUBLISHEDURL]=REPLACE([PUBLISHEDURL],'-DEV.DOMAIN.COM','-TST.DOMAIN.COM')
WHERE 
publishedtransferurl like '%-DEV.DOMAIN.COM%' 
or publishedurl like '%-DEV.DOMAIN.COM%'

puburls-PTCSVALUE-ntext-replace.sql and puburls-PTCSVALUEA-ntext-replace.sql

SET @oldString = N'por-pubcontent-dev.domain.com'; -- remove N 
SET @newString = N'por-pubcontent-tst.domain.com'; -- remove N 

That's it! Let me know if you find it as useful as I do! Enjoy!!