Ektron Upgrade on Replicated SQL Server Instance

I ran into an interesting situation this evening while prototyping an Ektron upgrade for a client. After running the site upgrade utility on my local instance, the database upgrade portion of the upgrade process failed miserably. A slew of pop-up error messages and then a final summary pop-up explaining that a handful of scripts did not succeed in running, "ERROR! Failed to execute script! Insert Error: Column name or number of supplied values does not match table definition."

After digging into the error logs and reviewing the SQL scripts that failed, I ran across an issue relating to SQL Server and database replication. Apparently, to track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. So, if a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. AND, if the table is dropped from the publication, the msrepl_tran_version column is NOT removed!!! ^*

I was dealt with having an entire Ektron CMS database that had an extra field/column in EVERY SINGLE table. Thankfully, dropping this field (and it's constraints) does nothing to Ektron. I'm not entirely sure what it does to the replication environment however.

I have included the SQL script I wrote to generate and run the alter statements to drop the offending field for anyone dealing with this issue. Feel free to comment if this is helpful.

And this did allow me to finally perform a successfully upgrade to 7.6.6 SP2.

if exists (
 select o.name from syscolumns c inner join sysobjects o on c.id=o.id where c.name='msrepl_tran_version'
) 
/*
 select 'alter table ' + object_name(parent_obj) + ' drop constraint ' + name + char(13) From sysobjects where name like '%msrep%' and type ='D' and object_name(parent_obj) <> 'MSreplication_subscriptions'
 select 'alter table ' + object_name(ID) + ' drop column msrepl_tran_version ' + char(13) from syscolumns where name like 'msrepl_tran_version%' and object_name(ID) not like 'conflict%'
*/
 
 declare @sql varchar(1000)
 declare mycurs cursor
 for select 'alter table ' + object_name(parent_obj) + ' drop constraint ' + name + char(13) From sysobjects where name like '%msrep%' and type ='D' and object_name(parent_obj) <> 'MSreplication_subscriptions'
 
 open mycurs
 fetch next from mycurs into @sql
 
 while @@FETCH_STATUS = 0
 begin
  exec (@sql)
  fetch next from mycurs into @sql
 end
 
 close mycurs
 deallocate mycurs
 declare mycurs cursor
 for select 'alter table ' + object_name(ID) + ' drop column msrepl_tran_version ' + char(13) from syscolumns where name like 'msrepl_tran_version%' and object_name(ID) not like 'conflict%'
 
 open mycurs
 fetch next from mycurs into @sql
 
 while @@FETCH_STATUS = 0
 begin
  exec (@sql)
  fetch next from mycurs into @sql
 end
 
 close mycurs
 deallocate mycurs
go