Adding a new article to Transactional replication

Hi,
I added a new article to an already existing publication via EM,
and reintialized it. When the snapshot agent ran the change was
replicated, but the snapshot agent took the snapshot of all the
articles in the publication about 85 of them, instead of just
replicating the change.
IS there a way to reintialize an article in the publication, so
snapshot agent would only snap that one article instead of the whole
publication?
Does this also apply to adding a new column or dropping a column from a
publication?
Thanks for your help.
GG
for sql 2000 replicating to sql 2000 subscribers, only the article should be
replicated. I am not sure what happened in your case.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GG" <gdabbara@.gmail.com> wrote in message
news:1117574970.901699.85980@.f14g2000cwb.googlegro ups.com...
> Hi,
> I added a new article to an already existing publication via EM,
> and reintialized it. When the snapshot agent ran the change was
> replicated, but the snapshot agent took the snapshot of all the
> articles in the publication about 85 of them, instead of just
> replicating the change.
> IS there a way to reintialize an article in the publication, so
> snapshot agent would only snap that one article instead of the whole
> publication?
> Does this also apply to adding a new column or dropping a column from a
> publication?
>
> Thanks for your help.
> GG
>
|||You mention that you reinitialized it - this is the reason why all articles
were created by the snapshot agent. After adding the article, you just need
to run the snapshot agent and then the distribution agent to get the single
article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your responses. I will try just starting the snapshot agent
instead of reintializing the whole publication. I was under the
impression that snapshot agent doesn't know about the change unless I
reintialize.
Thanks
GG
|||Adding an article is normally taken care of when running the snapshot. There
are some exceptions - if the initial publication was a nosync one, then the
snapshot of the new article won't generate anything by default. However
assuming this is niot the case you'll be fine.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

added article to current transactional repl. How to replicate additional article?

Hi, I already have transactional replication setup between two servers
(production server to reporting server).
I have created a new table witha primary key and have added this table as
an article to the publication.
How can i get my setup to take an initial snapshot of the table and apply it
to the reporting server?
Any help most appreciated!
thanks
John
John,
you will need to run the snapshot agent then synchronize. The whole process
from start to finish is:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start snapshot agent, start distribuition agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Are you saying that I have to take a snapshot of the whole database all over
again?
I thought I could take a snapshot of just the article that has been newely
added to the publication?
In actual fact, the 'Last Action' column on my agent (subsicriber' is saying
"The initial snapshot for article 'myTable' is not yet available"
Many thanks!
John
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQ2XvSVEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> John,
> you will need to run the snapshot agent then synchronize. The whole
> process from start to finish is:
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Start snapshot agent, start distribuition agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||John,
if you add a new table and subscription to that table, running the snapshot
agent won't create a complete snapshot - just the new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

Add new table to transactional replication with push subscriber

Hi there,

With SQL Server 2005 after adding a new table to replication, the entire snapshot is redone when initializing the snapshot agent. Ive read that with the pull subsciption there is a proc you can run to only snap the new object. Is there any way (besides creating a new publication) to stop the whole snapshot being redone for a push subscription?

Thanks

It seems there is no this kind of proc to only add snap new object, no matter for pull or push subscription. New added schema in publication will cause subscription to reinitialized , unless you manually add the new added objects into subscriber side and choose @.sync_type = 'none' to skip the initial snapshot.

Hope it will help.

Thanks

Yunjing

|||

Thanks for reply.

but surely this cant be right. in SS2000 only new objcts added were initialized. we have a database with over 40 GB replicated data and cant redo the whole snap every time another article is added.

|||A new snapshot is generated such that any new subscriber that is added can reference an up to date snapshot. The entire snapshot is NOT sent to the subscribers. Only the new objects are sent to each subscriber. So, you only get an incremental initialize when you add new articles to the publication.

Add new article to transactional replication

This one is driving me crazy...
We are using Transactional Replication with the subscribers initialized from
a backup (no snapshot). It's working great, but now we need to add several
new articles to the publication and have them picked up in the subscribers.
The only way I can get this to work is to drop the subscriber, backup the
published database, restore the previously made backup over the destination
databases and then re-create the subscriptions. There has to be a better
way?
I've tried using the sp_reinitializesubscription, but that doesn't work
since we aren't using a snapshot. I've tried skipping the restore, and just
creating the tables in both the source and destination databases. Doing
this, the subscription is re-created sucessfully, but data in the new
articles is not actually being replicated.
Replication isn't my forte, and the documentation on initializing with
backups is pretty sparse, so any advice is appreciated.
Use sp_addarticle followed by sp_addsubscription with @.sync_type =
automatic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Add Column to table in merge and transactional rep

Hi,
I have a table that is in both merge and transactional replication and I
would like to add a column to it. Typically, when I add columns to tables in
replication they only exist in one table, so I just go to the publication
properties and add it use the add column button. This time I am a unsure of
how to proceed though because I am not convinced that If I add it through the
interface of the merge publication that it will flow through in the
transactional replication.
So my solution is to add this to the merge replication as per normal and
reinitialize the transactional one and I wondered if anyone had an opinion on
this.
OK, through some experimentation I found that when I go to add a column to
one publication it allows you to choose any others that are applicable, so I
did and that worked great.

Add Article to Transactional

Greetings,
Have a transactional replication between 2 SQL 2000 servers. 1 is the publisher and the 2nd one is both distributor and subscriber.
Attempted to add an article to a publication via EM: right click the publication under 'Replication' folder and navigate to the
'Articles' tab. I 'showed' unpublished tables, check-marked the table I wanted added, got the warning screen about version compatibility, selected 'Apply' and then 'OK' to exit the properties page.
Of course the table does not appear in the subscriber db. I thought I followed what BOL said but I obviously missed something.
Anyone have any ideas as to what it was?
Thank you for your time.
Joe,
you need to run the snapshot agent to create the table snapshot, then run
the distribution agent to send it down.
Regards,
Paul Ibison

Add article to subscription

Transactional Replication - SQL 2000 - remote distributor
Have publication replicating 300 tables and I want to add 1 table.
sp_addarticle adds to publication, how do I add to each subscription (4)? Can
add schema to subscribers - new table so no rows exist anywhere.
How to add to subs in order to start replicating w/out re-synching each sub?
Thanks
Run the snapshot agent. This should generates a mini snapshot with only the
new article. The next time the distribution agent runs it should pick up the
new mini snapshot and distribute it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Transactional Replication - SQL 2000 - remote distributor
> Have publication replicating 300 tables and I want to add 1 table.
> sp_addarticle adds to publication, how do I add to each subscription (4)?
Can
> add schema to subscribers - new table so no rows exist anywhere.
> How to add to subs in order to start replicating w/out re-synching each
sub?
> Thanks
|||And if my table has 5 million rows - am I forced to do a snapshot. Can I do
a similar alternative init like making sure new column is out at subscriber
and then telling SQL that the subscriber does not need initialization?
Thanks,
Chris
"Hilary Cotter" wrote:

> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>
|||Oh - and don't I have to tell SQL to add it to one or all subscription(s)?
Thanks Hillary,
Chris
"Hilary Cotter" wrote:

> Run the snapshot agent. This should generates a mini snapshot with only the
> new article. The next time the distribution agent runs it should pick up the
> new mini snapshot and distribute it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E31D5A8C-5C44-4593-AEB1-8B8C05E7A5F0@.microsoft.com...
> Can
> sub?
>
>

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison

Add a clustered index in a published table

Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:

> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:

> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:

> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:

> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)