Replication of SP's mistake

When setting up replication of Stored Procedures & Views I made a mistake, I did not change the properties to tell replication to replicate the execution of the stored procedures.
How can I change this?
Originally, I just selected publish all & now when I look at Articles on the publication this is not checked, once the snapshot ran. I'm a bit confused.....
I want to the stored procedures to execute on my reporting/replicated db.
Thanx!
That's really not a very good option for me, is there any other way?
If I drop this publication, then I will need to reinitialize for >1000
tables only to get the stored procedures & views replicated? Not really
what I want to do, any other options available?
JUDE
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:023b01c4abcd$4d80f980$a301280a@.phx.gbl...
> The easiest way is to script out the publication then
> drop it.
> Change the scripts so
> @.type = N'proc exec'
> @.status = 0
> Then recreate the publication and initialize.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
I replicate my views and stored procedures in a separate publication to
avoid snapshotting the tables when my views change - perhaps this would be
an easier option for you?
Alternatively you could drop the subscription to each article and remove the
article eg:
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
The readd the article, run the snapshot agent and synchronize.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A separate publication sounds good, these views & SP's seem to be ever
changing at times.
I think I will script the publication, pull out the views & sp's, drop
subscriptions & articles, then setup a separate publication for the views &
sp's.
Another question...
What's the benefit of replicating the view execution (Actually, is there
such a thing?)
When I replicate views & SP's, can it be both schema & execution being
replicated to the reporting server, or an either or situation?
Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORXYiI%23qEHA.896@.TK2MSFTNGP12.phx.gbl...
> JLS,
> I replicate my views and stored procedures in a separate publication to
> avoid snapshotting the tables when my views change - perhaps this would be
> an easier option for you?
> Alternatively you could drop the subscription to each article and remove
the
> article eg:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> The readd the article, run the snapshot agent and synchronize.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
for views you don't get the same option.
For each stored proc article you get the options listed as the @.type options
in sp_addarticle. So, the 'structure' or 'structure and execution' are the
main options. If you select the latter, the schema will go down as well as
the execution, and it is not possible to select just the latter.
BTW,
I also
(a) apply a post-snapshot script to apply permissions.
(b) have a separate publication for udfs to force them to go first
(c) sometimes resort to sp_addscriptexec when the dependencies get mixed up.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)