Stopping replication, and updating the schema.
-
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.
-
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
-
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?
-
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 -
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.
-
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 -
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.
-
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...
-
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply