Index not replicated to the device

Hi,

I have a table published on the SQL2K Merger Replication publisher. But when I add an index to the table it's not replicated to the device. I have checked the snapshot folder and the newly added index is already in the .dri file. Any ideas?

Cheers,
Justin
One more thing to add, the index is not replicated to the existing subscriber but it works with new subscibers. Does that mean I have to drop and re-add subscription everytime a new index is added on the publisher?

Justin|||

Justin,

Replication supports a wide range of schema changes to published objects. When you make those schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers. However, Explicitly adding, dropping, or altering indexes is not supported. That is why you see the index is not replicated to the existing subscriber but it works with new subscribers.

You don't need to drop and re-add subscription every time a new index is added on the publisher, you just need to reinitialize the subscribers. Or you can add the index manually at subscribers Or using sp_addscriptexec to populate the index creation script to all the subscribers.

Hope it helps.

Wanwen

|||Thx, Wanwen.|||One more thing...as there will be no notification from the publisher or exception generated from the device during the replication after a new index is add but not pulled down to the device, is there a way we could know when to reinitialize the subscription?

Index in a table being replicated

Indexes can be added and removed without changing the
publication. If you do it manually on the publisher, you
can use sp_addscriptexec to run the same script on the
subscriber, or just create it manually.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks a lot,
If I need to add a new field in a table that is being replicated? How can I
do?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:40cb01c52022$fb3c7f30$a401280a@.phx.gbl...
> Indexes can be added and removed without changing the
> publication. If you do it manually on the publisher, you
> can use sp_addscriptexec to run the same script on the
> subscriber, or just create it manually.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jennyfer,
sp_repladdcolumn
Rgds,
Paul Ibison
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uxCsMnQIFHA.3472@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot,
> If I need to add a new field in a table that is being replicated? How can
> I
> do?
> Thanks in advance
> Jennyfer
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:40cb01c52022$fb3c7f30$a401280a@.phx.gbl...
>
|||Hello I added a new column using the sp_repladdcolumn. But I need to update
this new column or any other field in this table and I get the error:
The row was updated at 'SERVER-WMS.FSL' but could not be updated at
'NASH-WMS.FSL'. Procedure or function
sp_upd_80C179F0050B4837F57C4E2252BD409E has too many arguments specified.
The updates are being reversed
What can I do?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FAUHbWIFHA.3484@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Jennyfer,
> sp_repladdcolumn
> Rgds,
> Paul Ibison
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:uxCsMnQIFHA.3472@.TK2MSFTNGP09.phx.gbl...
can
>
|||Jennyfer,
have a look to see if this applies: http://support.microsoft.com/kb/825025
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It says in this page that there's a hotfix but it may receive additional
testing, or better wait for the next Microsoft SQL Server 2000 service pack
that contains this hotfix. How can I get this hotfix at no cost? Is it
possible? One thing I don't like is that this needs additional testing. This
is a bug.
Now I have 10000 conflicts and it's trying to update the data to other site.
How can I stop this error?
Thanks in advance
Jennyfer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u78WRp1IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Jennyfer,
> have a look to see if this applies: http://support.microsoft.com/kb/825025
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If you raise a support call with PSS and say that you
have this particluar issue they should give you the
hotfix for free. It'll be a link to a download page
specific to yourself.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Index gone in merge replication

I have a merge replication and I inserted 5000 records in the main publisher
and they were inserted in the alternate publisher and in 1 subscriber, but
replicating in the other 2 subscribers is very slow is taking forever. And
sometimes it fails by timeout. I see the server and sql is consuming 99%
because he's trying to insert these records and goes 100 by 100.
I looked at the table structure and the only thing different I see is the
index_2109250569 for rowguid field. Is this causing the problem that
replicating the records is taking hours? May I create this missing index in
the table without any impact for replication?
Thanks in advance
Jennyfer
I am not so sure it wouldn't have an impact (it might have an impact when
you are reinitializing the replication or things like that).
Anyways, I would create it to see if that improves the performance.
The worst case scenario if that the agent tries in the future to create the
index and it can't (because it is already there) and then it fails saying
that (and the solution would be to drop the index).
Jos.
"Jennyfer Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uG2bY4h5FHA.724@.TK2MSFTNGP14.phx.gbl...
> I have a merge replication and I inserted 5000 records in the main
publisher
> and they were inserted in the alternate publisher and in 1 subscriber, but
> replicating in the other 2 subscribers is very slow is taking forever. And
> sometimes it fails by timeout. I see the server and sql is consuming 99%
> because he's trying to insert these records and goes 100 by 100.
> I looked at the table structure and the only thing different I see is the
> index_2109250569 for rowguid field. Is this causing the problem that
> replicating the records is taking hours? May I create this missing index
in
> the table without any impact for replication?
> Thanks in advance
> Jennyfer
>