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 about setuping replication

Hi Guys,
I have two questions regarging setuping replication.
1. Do I need to configure Recovery Model as full when I want use
transactional replication.
2. Do I need to setup linked server when I use more than two servers for
replication.
3. Is there any doc about how to setup replication?
Thanks.
(1) No
(2) No but look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098
(3) BOL, Hilary's book and some general links on my site (eg
http://www.mssqlcity.com/Articles/Replic/Replic.htm)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Question about Nested Set Model - Hierarchical Data in SQL Server 2000

Hi,

I would like to convert my adjancey set model to a nested set model with a left and right.

I understood this part.

Root Node is always with a a Left = 1, right = 2*(Select Count(*) from Table)

How do the leaf node get the left and right numbers.

I was reading an article of Joe Celko regarding the Nested Sets in SQL and I could not understand how do the employee below Albert(who is top level manager) get their node numbers.

Adjanceny Model
--
Emp Boss

Albert Null
Bert Albert
Chuck Albert
Donna Chuck
Eddie Chuck
Fred Chuck

Nested Set Model
-
Emp Left Right
Albert 1 12
Bert 2 3
Chuck 4 11
Donna 5 6
Eddie 7 8
Fred 9 10

If anyone could kindly explain this to me, then I can try converting my table
to a nested set model to prevent recursion.

Thanks.

set nocount on
create table #chain
( seq integer not null primary key,
emp char (20),
chain varchar (20),
lft integer,
rgt integer
)

declare @.begDt datetime set @.begDt = getdate();

with organization (depth, emp, boss, chain)
as
( select 1 as depth,
emp,
boss,
cast(rtrim(emp) as varchar (400))
from tree
where boss is null
union all
select a.depth + 1 as depth,
b.emp,
b.boss,
cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
from organization a
inner join tree b
on a.emp = b.boss
)
insert into #chain (seq, emp, chain)
select row_number () over (order by chain) as seq,
emp,
chain
from organization

update #chain
set lft = 1 + 2 * ( select count(*) from #chain b
where b.seq < a.seq
)
- ( select count(*) from #chain c
where c.seq < a.seq
and ( c.chain = rtrim(c.emp) or
a.chain like '%, ' + rtrim(c.emp) + '%'
)
)
from #chain a

update #chain
set rgt = lft + 1
+ 2 * ( select count(*) from #chain b
where b.seq <> a.seq
and ( b.chain like '%, ' + rtrim(a.emp) + '%' or
a.seq = 1
) )
from #chain a

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

select emp, lft, rgt from #chain

-- - Output: -

-- emp lft rgt
-- -- -- --
-- Albert 1 12
-- Bert 2 3
-- Chuck 4 11
-- Donna 5 6
-- Eddie 7 8
-- Fred 9 10

-- Elapsed Time
--
-- 46

go

drop table #chain
go

|||

Thanks for the post. Can you please let me know the structure of your table Organization so that I can re-create it.

When I run the code at my SQL Server, I see the 2 errors:

Incorrect syntax near the keyword 'with'.

at the line:

with organization (depth, emp, boss, chain)

So I guess that that I do not have the Organization table and hence the error.

2nd error:

'row_number' is not a recognized function name.

at the line below:

select row_number () over (order by chain) as seq,

THanks.

question about guest account in model database...

i disabled most guest accounts in sql server soon as i set it up.
excepts the ones in master and temp dbs. but the guest accout in model
database says 'via group permission' and i couldn't delete it. should i
just leave it or do something about it? (if so, how). thank youI no group has got acces permissions to the database, you can leave it.
JN.
"=== Steve L ===" <steve.lin@.powells.com> a crit dans le message de news:
1111793365.858280.322730@.f14g2000cwb.googlegroups.com...
>i disabled most guest accounts in sql server soon as i set it up.
> excepts the ones in master and temp dbs. but the guest accout in model
> database says 'via group permission' and i couldn't delete it. should i
> just leave it or do something about it? (if so, how). thank you
>