January 10, 2007 at 11:22 am
I found this Article still don't know how. if a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed
I have another thought, may be we can turn on the transaction replication again and stop all the job schedule before we drop the publication to see if it will delete the msrepl_tran_version column
Le
January 10, 2007 at 1:08 pm
You found WHAT article?
If you drop a table from replication and the column is no longer needed, why don't you just drop the column?
-SQLBill
January 10, 2007 at 1:49 pm
I dropped the replication but the msrepl_tran_version column still in the table.
it cause problem when it interface wiht the inventory system, they don't regcognize the msrep_tran_version column that why we have to drop the replication.
I am not familiar with the replication but I found some help to tell me using enterprise manager to remove the column manually but I have more than 1800 tables.
January 22, 2007 at 6:22 am
Huong, I have the same issue with column msrepl_tran_version. Have you found a solution yet? Like you, I have too many tables to manually alter each table. I found the following script that works on non 'uniqueidentifier' columns. However, I can't get past the msrepl_tran_version dependency.
Here's the script:
declare @sSQL varchar(8000) set @sSQL = ''
declare @x varchar(100) set @x = ''
declare curMsrepl scroll cursor for
SELECT 'ALTER TABLE '+TABLE_NAME+' DROP COLUMN '+COLUMN_NAME+' '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name = 'msrepl_tran_version' + ' '
open curMsrepl
fetch next from curMsrepl into @x
while @@fetch_status=0
Begin
set @sSql = @x
--exec(@sSQL)
fetch next from curMsrepl into @x
end
close curMsrepl
deallocate curMsrepl
January 22, 2007 at 8:10 am
You do have to drop it manually, shouldnt hurt to do so as long as you've removed replication. Its only used by updateable subs (unless some programmer has decided to leverage it!).
January 22, 2007 at 9:23 am
Thanks for the post Andy.
The only thing I have in my favor, is the column isn't used anywhere in their Application. I've been dropping the column manually, but with 270+ tables, this could take awhile. The DBA uses Replication to get data into Development. So the next time I need new data, I'll be back to manually dropping columns. I was hoping I could work up a Stored Proc to execute after each data dump.
Oh well, I'll add that thought to my dreams of someday working with a perfectly normalized database. Thanks again for your input.
January 22, 2007 at 9:33 am
You only need the column if you're doing updateable subs. The easiest way to get a refresh of a subset of your names would be using snapshot replication (or transactional so that you can use a concurrent snapshot to avoid blocking on the publisher). Both can be set to just delete the contents of the existing table and then append all from the publisher.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply