October 4, 2005 at 3:46 am
Hello,
I have configued SQL server merge replication on SQL 2000. Its not allowing me to modify the design of any table. It gives me this error.
- unable to modify table.
ODBC error: [Microsoft] [ODBC Sql Server driver] [SQL Server] cannot drop the table 'dbo.Tablename' because it is being used for replication.
Thanks for your kind help.
October 4, 2005 at 7:48 am
If you have a large number of tables to modify, you would be better off dropping the replication - making your changes and then creating it again...if it's only a few tables, then I believe you can individually modify them and add them again using sp_droparticle and sp_addarticle...
However, I've not dealt much with replication so maybe some replication-guru on this site may come along with more comprehensive help!!!!
**ASCII stupid question, get a stupid ANSI !!!**
October 4, 2005 at 9:03 am
you can add columns and drop columns but that's about it when dealing with replicated tables. For that you use sp_repladdcolumn and sp_repldropcolumn respectively. If you need to perform changes that are more drastic you'll better off removing replication performing the changes and set it back on!
Cheers,
* Noel
October 4, 2005 at 9:10 am
noel - nice to have you concur (somewhat) with my post - i saw you come online a while ago & meant to say "hi"...pl. read my comment to steve's "our gang"...I'm still waiting for details from PASS..
**ASCII stupid question, get a stupid ANSI !!!**
October 4, 2005 at 9:37 am
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.
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 & subscription databases.
4) Ran just the portion of the CREATE Publication dealing with TableA (sp_addarticle, sp_articlefilter etc..)
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply