Add new SP without reinitializing

I have stored procedures that I want to add to the publication and
have them passed down to the subscribers. I don't want to
reinitialized everything as this would bring the subscriber database
offline during the sync. I tried just creating the SP on the
publisher, but it did not pass down.
1) How do I add a single SP without reinitializing?
2) How do I alter an SP and pass it down without reinitializing?
I'm using SQL2005 SP2.
Thanks,
Peter Cwik
On Dec 20, 12:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> I have stored procedures that I want to add to the publication and
> have them passed down to the subscribers. I don't want to
> reinitialized everything as this would bring the subscriber database
> offline during the sync. I tried just creating the SP on the
> publisher, but it did not pass down.
> 1) How do I add a single SP without reinitializing?
> 2) How do I alter an SP and pass it down without reinitializing?
> I'm using SQL2005 SP2.
> Thanks,
> Peter Cwik
Sorry--I'm using Transaction replication
|||Try this,
Add the article with sp_addarticle and specify @.type = 'proc schema only' &
@.force_invalidate_snapshot = 1
Add subscription with sp_addsubscription, this may throw error Msg 14100 if
your publication is setup with @.sync_method= concurrent, solution is to set
the publication with @.sync_method='database snapshot' (only applicable to SQL
Server 2005 SP2) or workaround is to bypass this check by passing this
parameter @.reserved = N'internal' to sp_addsubscription
"PeterCwik" wrote:

> On Dec 20, 12:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> Sorry--I'm using Transaction replication
>
|||On Dec 25 2007, 3:32Xpm, Manoj <Ma...@.discussions.microsoft.com>
wrote:
> Try this,
> Add the article with sp_addarticle and specify @.type = X'proc schema only' &
> @.force_invalidate_snapshot = 1
> Add subscription with sp_addsubscription, this may throw error Msg 14100 if
> your publication is setup with @.sync_method= concurrent, solution is to set
> the publication with @.sync_method='database snapshot' (only applicable to SQL
> Server 2005 SP2) or workaround is to bypass this check by passing this
> parameter @.reserved = N'internal' to sp_addsubscription
>
> "PeterCwik" wrote:
>
>
> - Show quoted text -
Well that sort of helps. I know it may seem like an obvious question
but, what does @.force_invalidate_snapshot = 1 really do? What are the
implications of invalidating a snapshot?
Thanks,
Peter Cwik
|||On Jan 2, 12:49Xpm, PeterCwik <pjc...@.gmail.com> wrote:
> On Dec 25 2007, 3:32Xpm, Manoj <Ma...@.discussions.microsoft.com>
> wrote:
>
>
>
>
>
>
> Well that sort of helps. XI know it may seem like an obvious question
> but, what does @.force_invalidate_snapshot = 1 really do? XWhat are the
> implications of invalidating a snapshot?
> Thanks,
> Peter Cwik- Hide quoted text -
> - Show quoted text -
I think I may have answered my own question...I just need someone to
validate this.
1. Transactional replication is setup and working fine
2. The publisher is the Prod database and subscriber is Reporting
database
3. Power users are allowed to add stored procedures to Reporting
database so now the two database are close copies but not completely.
4. My concern was that a resync would kill all the user created
objects in the Reporting database
5. It appears this is not so. The terms Synchronize, Snapshot and
Reinitialize are very different and have different meanings, though
even in this forum they are used incorrectly interchangeably.
6. If a new object is created (table, sp, view, etc.) a new snapshot
must be created. Creating a new snapshot does not delete data in the
Reporting database.
7. If continuous synchronization is used, any "new" objects that were
included in the new snapshot will be replicated to the Reporting
database. If continuous synchronization is not used, then "Start"
synchrozination must be chosen.
8. In 2005 changes to objects in existing subscriptions will
automatically be pushed from Prod to Reporting.
9. The ONLY time data in the Reporting database will be whiped out
and a fresh copy brought down from the Prod server is when a
Reinitialize is run.
10. Reinitialize looks at a Snapshot and pushes data and objects from
Prod to Reporting.
11. Only a Generate a Snapshot and Sychronize is needed to move
additional objects from Prod to Reporting. Reinitialize in NOT
required for the "additional" objects.
Hilary, does this sound right?
Thanks,
Peter Cwik