Altering a Published Table

Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:

Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.

Any ideas, what has gone wrong??

I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.|||Originally posted by joejcheng
Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.

No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||When you add a columns that is not PK, and you dont wish to reinitiliase during operation hours, you can choose this way, try to create a column by using Publisher properties, go to Filter columns in Publisher properties, then add the column using the Add column button, and then dont tick on that column you just added. Now you didnt ask merge agent to replicate that column for you. What you doing is to add a column for your user to make use of that column in the publisher database server. Wait till you have time to replicate the whole snapshot off the operation hours. Good Luck !

Originally posted by TALAT
No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||Thanx for your help eshl. I use to apply the SNAPSHOT only when there's a change in the PK or an ID seed column of a published table. The publication-database has 24 hours production and the subscribers have online websites running on them. I am eagerly lookin for a way to completely bypass the SNAPSHOT.|||http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20800513.html

altering a column of a published table (trans repl)

Hi,
In BOL there is reference only to sp_repladdcolumn and sp_repldropcolumn
(add and drop), but nothing about altering a column, or have I missed it?
I have to change the collation of a column (that belongs to a table that is
transactionally replicated ) from sensitive to insensitive. Is the only way
to do that is to:
1 - add a New column with the correct collation (sp_repladdcolumn )
2 - copy the data from the old column into the New column (update statement)
3 - drop the old column (sp_repldropcolumn)
Any better way? or have I missed anything?
Thanks
You didn't miss anything - that's a problem we all have faced at some time
or other . Actually there's another level of iteration you missed out, as
your table will be missing the column with the oldname, so if this is to be
maintained you have to do the whole process again. There is an alternative
of dropping the subscriptions to the table, removing the table from the
publication, altering the table then readding to the publication then adding
subscriptions to this table. In this way you can effectively reinitialize on
a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
Table statement will itself be sufficient for most things.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
right, since add comes before drop, I would need 2 pairs of (add, drop ) ;
the first to alter the collation, the second to alter the column name (to
set it back to the original name) - correct?
and probably to have these 4 sp_Replxxx bracketed by Begin Tran - Commit
Tran.
is the alternative you indicated better in some ways (safer, faster, ...) ?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It's potentially less processing time - largely depends on the 'width' of
your table, ie for a table not especially wide then I'd do the drop method.
if I had 200 columns, I'd do the column technique.
Cheers,
Paul Ibison
|||Thank you very much !
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#A2HWS1yFHA.464@.TK2MSFTNGP15.phx.gbl...
> It's potentially less processing time - largely depends on the 'width' of
> your table, ie for a table not especially wide then I'd do the drop
method.
> if I had 200 columns, I'd do the column technique.
> Cheers,
> Paul Ibison
>
|||Hi Paul,
Now I am in troubleshooting mode
I dropped the subscriptions to the table to alter, dropped the article (tale
from the publication), altered the table, added the article back to the
publication, and added the subscriptions to the publications. The schema
changes to the table became effective and were replicated to the destination
table (in the subscription), but the snapshot agent failed when I ran it.
The error is: The process could not create file....
I searched on MS and found a couple items (285997, 821480), but I am not
sure.
Any ideas?
The details:
I have 3 publications, each has few articles. There is one subscriber, and
only one subscription to all publications.
I did the following,
EXEC sp_dropsubscription @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.subscriber = 'SubscriberServer'
, @.destination_db = 'Dest_DB'
EXEC sp_droparticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
ALTER TABLE Orders_2 ALTER COLUMN ....
EXEC sp_addarticle @.publication = 'Pub_2'
, @.article = 'Orders_2'
, @.source_table = 'Orders_2'
, @.destination_table = 'Orders_2'
, @.force_invalidate_snapshot = 1
-- the next is from scripting out the publication (prior to making the
changes)
EXEC sp_addsubscription @.publication = N'Pub_2'
, @.article = N'all'
, @.subscriber = N'SubscriberServer'
, @.destination_db = N'Dest_DB'
, @.sync_type = N'automatic'
, @.update_mode = N'read only'
, @.offloadagent = 0
, @.dts_package_location = N'distributor'
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#CM4dxyyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> You didn't miss anything - that's a problem we all have faced at some time
> or other . Actually there's another level of iteration you missed out,
as
> your table will be missing the column with the oldname, so if this is to
be
> maintained you have to do the whole process again. There is an alternative
> of dropping the subscriptions to the table, removing the table from the
> publication, altering the table then readding to the publication then
adding
> subscriptions to this table. In this way you can effectively reinitialize
on
> a table basis. All MUCH easier in SQL Server 2005 of course - the Alter
> Table statement will itself be sufficient for most things.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Ramadan,
please check to see if there is an automatic virus-scanner set up. If so,
disable scanning of the repldata folder.
Also, check that there is space in the distribution working folder to create
the file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
it was as simple as a missing folder could be.
Thank you very much for your help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oa2$isDzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Ramadan,
> please check to see if there is an automatic virus-scanner set up. If so,
> disable scanning of the repldata folder.
> Also, check that there is space in the distribution working folder to
create
> the file.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Alter column in a database that is a transactional replication publisher

When I need to add columns to my publisher in a transactional replication I
use the sp_repladdcolumn. How do I Alter a column with no need to stop and
recreate the replication?
@.force_reinit_subscription has a default of zero, so I'm wondering if you
set this explicitly to 1? If not, are you really getting a reinitialization,
or just an indication that the existing snapshot is no longer applicable?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .