Deleting Replication Objects

Question

Post reply

Deleting Replication Objects

Steve Williams-210812

SSC Eights!

Points: 901
More actions
April 19, 2005 at 8:49 am

#85945

Hi there. I removed Replication but am now stuck with the MSPub_identity_range table still being in the list of data tables in my databases. I can't drop it because it's a system object. Is there a quick and easy way of dropping this table from the database? Would I have to delete all the sysobjects entries for it? If so, what are they?
Many thanks
Kindest Regards,
Steve Williams

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Williams-210812 SSC Eights! Points: 901 More actions · Answer 1

Will the below be suffice?

DELETE FROM SysObjects WHERE Name = 'MSpub_identity_range'

Kindest Regards,

Steve Williams

SQL Draggon Right there with Babe Points: 766 More actions · Answer 2

have you used the proc called "sp_removedbreplication dbname". This will remove all replication objects in the database.

Good luck

Don't count what you do, do what counts.

SQL Draggon

wjwGeorgia SSC Veteran Points: 223 More actions · Answer 3

I did this a little while ago. I was stuck because you have to drop the constraints on the replicated column before you can drop the column. Even though all the columns have the same name, all the constraints are uniquely named. What I did was query the system tables based on the parts of the names I could count on being there. I had my queries create the code to remove the objects. I did not want to edit the system tables directly, it is better to let sql server manage its internal structure.

BE SURE TO LOOK CAREFULLY AT THE STATEMENTS CREATED BY THESE SELECTS BEFORE RUNNING THEM!!!

-- run this from the database where you have the replicated columns

-- create code to remove constraints

select 'Alter table ',T.name, 'drop constraint', C.[name]

from sysobjects C inner join sysobjects T

on c.parent_obj = T.[id]

where C.[name] like '%__msrep__%'

--output looks like:

/*

Alter table TBLLEADSOURCEMASTR drop constraint DF__TBLLEADSO__msrep__004D51F6

Alter table TBLSALESMANMASTERAUDIT drop constraint DF__TBLSALESM__msrep__00EC7074

Alter table tblServiceOrderTypes drop constraint DF__tblServic__msrep__30D08DC0

Alter table TBLBONUSJOBSII drop constraint DF__TBLBONUSJ__msrep__311084E2

…

*/

-- create code to remove columns

select 'Alter table ',T.name, 'drop column', C.[name]

from syscolumns C inner join sysobjects T

on c.[id] = T.[id]

where C.[name] = 'msrepl_tran_version'

-- output looks like:

/*

Alter table TBLSALESMANMASTER drop column msrepl_tran_version

Alter table TBLCALENDARMASTER drop column msrepl_tran_version

Alter table TBLSALESMANCOMPMASTER drop column msrepl_tran_version

Alter table tblCommissionMaster drop column msrepl_tran_version

…

*/

Hope this helps.

Steve Williams-210812 SSC Eights! Points: 901 More actions · Answer 4

I'll take a look at this and give it a go. Many thanks

Kindest Regards,

Steve Williams

Frederik du Plooy Grasshopper Points: 24 More actions · Answer 5

Flipped wjwGeorgia! If I had a daughter I would mail her over to you immediately. You helped me out of one sticky situation on our live server with that code snipped.

a3b2c1r46 SSC Journeyman Points: 85 More actions · Answer 6

I know this topic is very old but the solution scripts worked great for me. I had to edit out some trash entries but made the process much easier with about 40 tables to clear out the mstrans_repl junk.

Thanks!

Side note: this totatly killed the db's application shortly after... had to create a publication to add the fields back to fix it... oh well... just fyi