Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.
Any idea?
Thanks!
Hi Alan,
In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.
This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||
Hello Mahesh,
Background:
I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.
Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.
Before opening up a case do you have any recommendations? (or anyone else for that matter)
Thank you in advance.
|||Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.
update sysdatabases
set category = 0
where name = <your database name>
|||A better way is to try:
If using Merge replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'
If Tran replication:
exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'
See if that serves the purpose.
|||Mahesh,
I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin
|||Sachin,
Thank you. What I did was the following:
1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.
2. I attempted to run your script above but I did not have ad hoc updates on.
3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.
4. Then I created a drop database query and ran that.
5. That dropped the table from the system just fine!
Thank you.
|||Don,
I am glad I could help. :)
|||Sachin,
Me too. Step 5 should have been "dropped the database" instead of table.
Best Regards~
Don
