Cannot drop the table because it is being used for replication.

[Fulya Erol]

I have replication setup between two SQL 2000 servers, I want to change the
size of a column on the publisher and I get the following error. How can I
modify a column if it's replicated?
Cannot drop the table 'dbo.test' because it is being used for replication.

Thanks in advance..

[Fulya Erol]

Here is what I did;
Right -click and go to properties on the publication
Choose filter columns tab
I unchecked the column I wanted to exlude from replication
Then I went to subscriptions tab and then hit reinitialize
Then I went back to the snapshot agent and start the agent.
As soon as I did that it start recreating and replicating the whole
database. I just wanted one column to be dropped but instead it replicate
the whole database, all the tables and columns which took forever.

What do you think I can do? they told me that modifying is easier to do in
SQL2000 :(

Thank you for your help.

"Paul Ibison" <Paul....@Pygmalion.Com> wrote in message
news:D928C05E-A385-4F41...@microsoft.com...
> Fulya,
> this is a common request, but in SQL Server 2000 is not possible directly
(see Yukon!). There is a workaround:
> You could add a new column with the new datatype (sp_repladdcolumn), do an
update on the table to populate the column, then drop the column
(sp_repldropcolumn). Do this again to create the column having the same
original name.
> Regards,
> Paul Ibison (www.replicationanswers.com)

[Fulya Erol]

I think I had to learn a lot about replication. If the database is published
and I want to add a new table to teh database, and I also want this table to
be replicated , too? what do I need to do? obviously when I create the table
it's not published immediately. Can you please direct me on this?

Thank you so much,
Fulya

"Paul Ibison" <Paul....@Pygmalion.Com> wrote in message

news:2C50B733-5AB9-4B6E...@microsoft.com...
> Fulya,
>
> Execute sp_repldropcolumn with a value of 0 for the
@force_invalidate_snapshot parameter. This parameter affects only
publications created with the immediate_sync option. To see if this option
is set for your subscription use:
>
> sp_helpsubscription @publication = 'northwindnewregion' : synchronization
type = 1 so automatic ie immediate_sync
>
> sp_repldropcolumn @source_object = 'tablename'
> , @column = 'columnname'
> , @force_invalidate_snapshot =0
> , @force_reinit_subscription = 0
>
> This should prevent the need for reinitialization and a new snapshot.
There's no need to reinitialize subscribers manually - this definitely will
cause the snapshot to be applied. All you need to do is synchronize.
>
> Regards,
> Paul Ibison
>

[Mario Splivalo]

On 2004-03-04, Paul Ibison <Paul....@Pygmalion.Com> wrote:
> Fulya,
> you'll need to run sp_addarticle which will force a resynchronization (creation and application of snapshot files) by default. There is a way to add the new article and not to force the complete snapshot which I posted a couple of weeks ago in this newsgroup - it's also in the transactional section of www.replicationanswers.com.
(and where is line break?) :)
> Regards,
> Paul Ibison

You can achive all of this troug the enterprise manager. Here's what you do:

Right click on the publication, select properties, then go to 'filter
collumns', click on 'add columnt to a table' (or 'drop selected collumn').
SQL server will warn you about the compatibility issues, ignore that if both
your publisher and subscriber are SQL2000. They say you don't need to
recreate the snapshot, but I did so (my tables aren't holding large amounts
of data). Voila, you added new column to a publicated table.

If you want to add a new table to publication - well, you can't. But,
nothing is stopping you from adding a new publication with just one article
- the newly created table.

Mike

--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."

Mario Splivalo
mspl...@jagor.srce.hr

[Fulya Erol]

I did all that, but when I recreate a snapshot, it takes snapshot of all the
tables in the database not only the modified one. Did you find a solution to
that?

Thanks,

Fulya

"Mario Splivalo" <ma...@fly.srk.fer.hr> wrote in message
news:slrnc4ooi...@fly.srk.fer.hr...

[Paul Ibison]

Mario,
there seem to be two concurrent threads here - originally Fulya was asking about adding columns, then later on in the thread he asked another separate question about adding tables. My previous post was related to the second issue of adding articles to an existing publication.

Yes - it is possible, but not from the gui. You can use sp_addarticle followed by manually creating scripts and sp_addsubscription (push) or sp_refreshsubscriptions (pull).

Regards,
Paul Ibison

[Mario Splivalo]

On 2004-03-08, Fulya Erol <fe...@no.nospam.mezun.com> wrote:
> I did all that, but when I recreate a snapshot, it takes snapshot of all the
> tables in the database not only the modified one. Did you find a solution to
> that?
>

Bhm?
That's odd. Do you have all of you tables publicated in one publication? If
that's the case, you should reconsider your replication design.

When you recreate the snapshot, all the tables within that publication will
be taken into consideration :) by the snapshot agent.

[Mario Splivalo]

On 2004-03-08, Paul Ibison <Paul....@Pygmalion.Com> wrote:
> Mario,
> there seem to be two concurrent threads here - originally Fulya was asking about adding columns, then later on in the thread he asked another separate question about adding tables. My previous post was related to the second issue of adding articles to an existing publication.
>

Ups, my bad, I appologize.

> Yes - it is possible, but not from the gui. You can use sp_addarticle followed by manually creating scripts and sp_addsubscription (push) or sp_refreshsubscriptions (pull).

Have you ever done that? Somehow it seems for me it's easier to drop the
publication and recreate it all over again.

[Mario Splivalo]

On 2004-03-09, Paul Ibison <Paul....@Pygmalion.Com> wrote:
> Mario,

> I must admit haven't used it in production, but there was a question a few weeks back where a guy had a few massive articles already set up in a transactional replication publication and he wanted to add a small blank table on the subscriber and publisher without requiring a new snapshot, so in this case it seemed like the best solution.

Yes, it does seems like the best solution, but, when I tried something like
that in real life, i ended up with broken replication :)

That why I was suggesting 'all over again' solution... in my case, it would
have saved me a lot of time.

[Paul Ibison]

Mario,
This solution is tried out in our test environment with no issues
and a similar solution exists
on Vyas's site (http://vyaskn.tripod.com/repl_ques.htm).
If you try it again and have an error, please
post it up - I'd like to have a look at it.
Regards,
Paul Ibison

[Mario Splivalo]

On 2004-03-09, Paul Ibison <Paul....@Pygmalion.Com> wrote:

I will definitly do so, thank you for the link. I'll post the results here.