Last week was a busy week for us. We rolled out a new version of Arena that included significant changes to the event administration area, and also switched over to using the new “Arena” templates instead of our old “Solomon” templates. At the same time we decided to upgrade to our new SQL 2005 database server, rename the database from ccvSolomon to Arena, and rename all our domain names from Solomon to Arena (for example, we now use http://www.ccvonline.com/arena, instead of http://www.ccvonline.com/solomon).
As you can imagine, there was a big ripple effect. The database server and database name changes weren’t too hard to manage, since we just had to change the connection string for each of our sites (Intranet, Website, Stars, and Garage), and every application (checkin, registration, automation agents, etc).
The URL change was a little more difficult. It was easy enough to change url paths in config files and organization settings, however, we also had a lot of HTML content that had references to the old URL path (links, images, etc.). All of this content had been entered using the Arena HTML module and was stored as a Text datatype column in our database. Unfortunately, you can’t use the SQL replace function on a text column, but I had stumbled upon a little SQL script a while back that helped a lot with this…
set xact_abort onbegin tran
declare @otxt varchar(1000)
set @otxt = ‘http://solomon.ccvonline.com/solomon’
declare @ntxt
varchar(1000)
set @ntxt =
‘http://arena.ccvonline.com/arena’
declare @txtlen int
set
@txtlen = len(@otxt)
declare @ptr
binary(16)
declare @pos int
declare @id int
declare curs cursor
local fast_forward
for
select id,
textptr(module_details),
charindex(@otxt, module_details)
-1
from port_module_instance
where module_details like ‘%’ + @otxt +’%’
open curs
fetch next
from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
print ‘Text found in row id=’ + cast(@id as varchar) + ‘ at pos=’ +
cast(@pos as varchar)
updatetext port_module_instance .module_details @ptr
@pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs
commit tran
I modified the script to do a search and replace on the “module_details” column of our “port_module_instance” table, which fixed all of our old links.
Needless to say, I didn’t get much coding done last week as I worked through small issues with all the changes we made, but today is a new week….
- David
