Post reply

Deleting Replication Objects

  • 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

  • April 19, 2005 at 8:59 am

    #553454

    Will the below be suffice?

    DELETE FROM SysObjects WHERE Name = 'MSpub_identity_range'

    Kindest Regards,

    Steve Williams

  • April 20, 2005 at 9:19 am

    #553818

    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

  • April 20, 2005 at 3:36 pm

    #553931

    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.

  • April 22, 2005 at 2:52 am

    #554289

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

    Kindest Regards,

    Steve Williams

  • June 13, 2006 at 9:02 am

    #643424

    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.

  • June 23, 2009 at 8:40 pm

    #1014397

    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

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

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