Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

Question : Transactional Replication Rollback ?

We have a sql2k db as a Distributor, with a few servers geographically
scattered acting as subscribers (backup). I'm setting up transaction
replication, however, is there a way to rollback in the event of
dataloss on the Distributor ?
Eg : 'delete tblProducts' run on the distributor. Wouldn't this
effectively delete tblProducts on every subscriber ? Is there anyway
to rollback in such an event.
Thanks,
Amit Chandel
Amit,
presumably when you say Distributor, you mean Publisher/Distributor?
If you run the TSQL "Delete * from Table1" on the publisher this will be
propagated via the logreader and distribution agents to the subscriber.
Turning off these agents before the command gets to the subscriber will
prevent the delete. Once it has occurred at the subscriber, you can not roll
it back. One posibility is you could back up the transaction log and do a
point-in-time restore. If the command is potentially 'dodgy' then you could
also wrap it in a transaction with normal error trapping and ROLLBACK
statements. If you give the transaction a name, it is possible to do a
restore as mentioned above, but not to a point in time, rather to just
before the named transaction was run.
HTH,
Paul Ibison