Does transactional replication replicate SPs?

I set up TR on table T1. Works fine.
While it was running, I tried adding an article, mysp.
I couldn't quite figure out what to do to send the initial snapshot of
just mysp, while replication was already running for T1.
Eventually I ... well, I lost track. I think I deleted the
subscription, then pushed it again, then finally told it explicitly to
run the snapshot agent immediately. OK, the SP travelled.
So, do I now have both the table and the SP replicating?
No. I can add rows to the table on the publisher, and gosh if they
don't show up on the subscriber within about a minute. But I can
modify the SP on the publisher, and the changes to not show up on the
subscriber.
So, I'm wondering, is that even supposed to work?
Thanks.
Josh
Josh,
Changes to stored procedures and other database objects do not replicate in
SQL Server 2000. You have to wait for SQL 2005 to do that!
Jon Jahren
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||Replication of stored procs is intended to be a one time event. If you
modify procs frequently and you wish to replicate them bundle them up in a
script and use sp_addscriptexec to deploy them to all subscribers. The proc
sp_addscriptexec only works for subscriptions deployed via UNCs. If are
using FTP subscribers you will have to create a seperate publication to
deploy your procs. It seems that the procs schemas are cached and if you
redeploy the snapshot after modifying the procs the updates won't be
replicated. You are best to drop the proc publication and recreate it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||On Thu, 11 Nov 2004 11:33:11 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:
>Replication of stored procs is intended to be a one time event. If you
>modify procs frequently and you wish to replicate them bundle them up in a
>script and use sp_addscriptexec to deploy them to all subscribers. The proc
>sp_addscriptexec only works for subscriptions deployed via UNCs. If are
>using FTP subscribers you will have to create a seperate publication to
>deploy your procs. It seems that the procs schemas are cached and if you
>redeploy the snapshot after modifying the procs the updates won't be
>replicated. You are best to drop the proc publication and recreate it.
Thanks to you and Jon.
Since the dialog lets you click tables and procs equally, and BOL is
not terribly clear on the matter, it's easy to think the procs do
replicate.
But, they do replicate in Yukon?
And back here in SQL2K, I thought there was something in BOL about
replicated SP's also executing on the subscriber, ... or is that
another confusion?
http://msdn.microsoft.com/library/de...limpl_0d2m.asp
I'd appreciate further insight on this.
Thanks!
Josh
|||On Thu, 11 Nov 2004 09:55:38 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>And back here in SQL2K, I thought there was something in BOL about
>replicated SP's also executing on the subscriber, ... or is that
>another confusion?
>http://msdn.microsoft.com/library/de...limpl_0d2m.asp
>I'd appreciate further insight on this.
Also, the radio button that is supposed to turn on this SP execution
replication is greyed out on my dialog. Why would this be (because it
is an existing? Yes! Just as the warning box suggests, to enable it
I guess I'd have to drop and reinitialize, since when I add a new SP,
the option is enabled. Guess I don't have a question here after all,
but could still use any further info anyone cares to drop on me.)
J.
|||You could drop the article from the subscription and readd if you don't want
to reinitialize the complete publication.
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
sp_refreshsubscriptions @.publication ='northwindxxx'
To Readd, just do the reverse. Running the snapshot agent will snapshot just
the article you need.
To avoid this type of issue, I tend to put all the stored procs in a
separate publication.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)