Mike,
if you are using transactional replication, have a look at
the article properties for the stored procedure. There is
the option to replicate execution of the stored procedure.
AFAIR by default this is not done, so the stored procedure
itself (rows in sysobjects and syscomments etc) is
replicated. Changes to the stored procedure TSQL are not
replicated in the future, as this would be replication of
system table data which we can't yet do.
Replication of the execution of the stored procedure and
the rows affected are 2 separate issues and have no
overlap.
HTH,
Paul Ibison
Hi, Paul. Thanks for responding.
Maybe I need to restate my question.
Actually the execution of the SP and the rows affected was my question.
In particular, if a table is being replicated and a stored procedure that
inserts, updates, deletes rows from that same table is replicated, how does
replication know only to execute the SP and not to transfer the rows
affected by the execution of the SP?
So, for example, if a developer inserts 8,000 records into a replicated
table via a stored procedure, I don't want the 8,000 rows to be transferred
to the subscriber, but rather the "EXEC" statement.
Any idea if/how replication handles this?
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d6701c46fd0$e2744d50$a401280a@.phx.gbl...
> Mike,
> if you are using transactional replication, have a look at
> the article properties for the stored procedure. There is
> the option to replicate execution of the stored procedure.
> AFAIR by default this is not done, so the stored procedure
> itself (rows in sysobjects and syscomments etc) is
> replicated. Changes to the stored procedure TSQL are not
> replicated in the future, as this would be replication of
> system table data which we can't yet do.
> Replication of the execution of the stored procedure and
> the rows affected are 2 separate issues and have no
> overlap.
> HTH,
> Paul Ibison
|||I'm not sure how it knows, but it knows!
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mike" <MichaelLopez@.inds.com> wrote in message
news:OplHRT$bEHA.2940@.TK2MSFTNGP10.phx.gbl...
> Hi, Paul. Thanks for responding.
> Maybe I need to restate my question.
> Actually the execution of the SP and the rows affected was my question.
> In particular, if a table is being replicated and a stored procedure that
> inserts, updates, deletes rows from that same table is replicated, how
does
> replication know only to execute the SP and not to transfer the rows
> affected by the execution of the SP?
> So, for example, if a developer inserts 8,000 records into a replicated
> table via a stored procedure, I don't want the 8,000 rows to be
transferred
> to the subscriber, but rather the "EXEC" statement.
> Any idea if/how replication handles this?
> Thanks again,
> Mike
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:1d6701c46fd0$e2744d50$a401280a@.phx.gbl...
>
|||So, then the answer to the "If" part is "Yes, the EXEC command is
transferred, not the changed rows", and in that case the "How" part really
is irrelevant.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23cHgXRFcEHA.3792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I'm not sure how it knows, but it knows!
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mike" <MichaelLopez@.inds.com> wrote in message
> news:OplHRT$bEHA.2940@.TK2MSFTNGP10.phx.gbl...
that
> does
> transferred
>
Showing posts with label properties. Show all posts
Showing posts with label properties. Show all posts
Friday, March 9, 2012
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)
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)
Subscribe to:
Posts (Atom)