Replication of Stored Procedures

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
>