Stopping replication, and updating the schema.

  • Cayley

    Say Hey Kid

    Points: 665

    Hi,

    I have a database which replicates out to 4 international sites, normally fairly successfully.  However, the software provider has released an upgrade including changes to the schema.  The update script fails as it can not drop some of the view's / procedures because they are being used for replication.

    eg

    Server: Msg 3724, Level 16, State 2, Line 325

    Cannot drop the view 'dbo.V_XXXXXXXXX' because it is being used for replication.

    Server: Msg 3724, Level 16, State 2, Line 1

    Cannot drop the procedure 'dbo.SP_ZZZZ' because it is being used for replication.

    I have stopped the replication, deleted subscribers and disabled the publisher.  How can I get rid of the remaining traces of replication so that I can upgrade the database (I presume there is probably a better way of doing this than killing all replication, but it's too late now!).

    Thanks in advance for your help.

     

  • Ray M

    SSC-Insane

    Points: 21093

    You need to use alter view statements.

     

    and or use the replication procedures to modify schema.

    http://msdn2.microsoft.com/en-us/library/ms151870.aspx

     

     

  • Cayley

    Say Hey Kid

    Points: 665

    Thanks Ray.

    The script I want to run is from the software provider and will invariably have to run similar scripts again with later versions.  What I would really like to do at this point is remove all traces of the replication (rowguid columns etc) and start from scratch this time being more selective over the articles I publish.  This way I only publish data not all the views, procedures etc which are dealt with in the upgrades.

    Any ideas?

  • debrucer1

    SSCertifiable

    Points: 5366

    This sounds like EXACTLY what I am trying to develop... and I KNOW that my requirements are not so unique that it hasn't been done before.  This should be a very common scenario.

    My current thinking, unless someone comes up with a tool that already does all of this, is that I will use "scptxfr.exe" to make a before and after file of my table structures, and then a tool, currently looking at "Beyond Compare 2", to create the difference report between the two, and then some other tool, like a Unix type SED editor, to make the changes to the scripts created from SQL Server when you hit "Generate SQL"...

    But the details aren't trivial, and my hours are rapidly running out to get it together.

    I CAN'T BELIEVE someone hasn't done this before...

    Comeone somebody... give us a tool!  It has to be lurking out there somewhere.

    David

     

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • homebrew01

    SSC Guru

    Points: 55186

    I did this with good results when I needed to change the length of a field in a replicated table. -- Specifically: Drop a table from a publication so it can be modified, then add the table back in. I've done this with snapshot & trans replication, so I hope it works with merge too. Perhaps you can test it and see if it (or a variation) will work for you:

    1) I just scripted out the CREATE Publication and the DROP publication commands to text files. This creates the sp_droparticle & sp_addarticle commands for you (& other commands if applicable).

    2) Ran just the portion of the DROP publication that had to do with TableA (sp_droparticle)

    3) Modified FieldA from 6 char to 8 char on Publication AND Subscription databases.

    4) Ran just the portion of the CREATE Publication dealing with TableA (sp_addarticle, sp_articlefilter etc..) to put the tables back in

    5) Ran the snapshot job

    6) Ran the pull subscription job

    7) Checked the table in the subscription database & it has the same data as the source table.

    I usually set up a test replication and try out these things ahead of time.

    This procedure works pretty simply when you have a few changes to make.

  • debrucer1

    SSCertifiable

    Points: 5366

    and you are saying that the snapshot refresh was ONLY for the part of the snapshot affected?  (i.e., only included TableA?).  I thought I tried something similar; but when I ran the snapshot and PUSH subscription job, it did the whole snapshot... taking something like an hour and a half.

    I have seen your posts before on this subject... and hope it works as I understand you to write.  That will be the finishing piece to my "diff script"... now, just to automate it all

    David

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • homebrew01

    SSC Guru

    Points: 55186

    I don't remember if it was only for the affected table. Run a test scenario, and look in the history of the subscriber job and it will show you which tables were brought over in the snapshot.

  • Doug Elliott

    SSC Enthusiast

    Points: 142

    I've got a very similar problem - I'm trying to move & update a DNN website...

    We originally setup replication on the 'old' servers which are running SQL2000.

    We removed replication and I have confirmed that there are no publications or subscriptions present now.

    I've taken a backup of the SQL2000 db and restored it on the new SQL2005 server (which is a brand new SQL install).

    When I run the update (which tries to drop a bunch of stored procedures prior to installing the new versions) I get the following type of error message.

    "Cannot drop the procedure 'dbo.Get...' because it is being used for replication.

    So... there must be something leftover from the attempt we made at using replication 5 years ago on the old SQL2000 system (and then removed) - even though there isn't any visible remnant...

    Is there anyway to clean whatever is left behind that's making the install think that replication is still present? :crazy:

    Talk about frustrating... we need another emoticon with hair being pulled...

  • Doug Elliott

    SSC Enthusiast

    Points: 142

    Further research led to sp_removedbreplication

    Ran that against my db and was able to get the stored procedures to install properly.

    -Doug

Viewing 9 posts - 1 through 9 (of 9 total)

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