Adding table/article w/o starting snapshot!

Before running the snapshot agent, run
sp_refreshsubscriptions 'publicationname'
Cheers,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for ur response, but I still get the same error.
"A snapshot was not generated b/c no subscription needed intialization".
Regards
Naveed.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1aa701c4aa03$e43f6440$a501280a@.phx.gbl...
> Before running the snapshot agent, run
> sp_refreshsubscriptions 'publicationname'
> Cheers,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Naveed,
was the initial subscription a noinit one?
It looks like it was, in which case you'll have to
manually add the scripts
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;299903), and DTS the table. Alternatively you can
publish the table in a separate publication.
Rgds,
Paul Ibison[vbcol=seagreen]
|||Hello Paul;
I think if I reintialize the subscription then snapshot will start
publishing all articles instead of 1 article.
and I dont want that.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3fb201c4aa11$91875d30$a601280a@.phx.gbl...
> Naveed,
> was the initial subscription a noinit one?
> It looks like it was, in which case you'll have to
> manually add the scripts
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;299903), and DTS the table. Alternatively you can
> publish the table in a separate publication.
> Rgds,
> Paul Ibison
>
|||Naveed,
to recap - from your other posts you mention that running the snapshot agent
doesn't do anything. After adding a new article and refreshing the
subscription, still the snapshot agent doesn't do anything. My assumption is
that the initial setup was a noinit one, in which case no snapshot of a new
article is produced. The new article is a part of the publication and (I'm
not suggesting you try this) if you update a record in this article on the
publisher there should be an error in the distribution agent which complains
about the absence of an update stored procedure onthe subscriber. Now you
have a choice -
(a) set up these stored procedures by hand using
sp_scriptpublicationcustomprocs and transfer the table by hand. Be sure to
remove the identity attribute if there is one.
(b) reinitialize.
(c) add the new article to a separate publication. This can cause integrity
problems if the article is related to other articles in the first
publication.
I have suggested (a), which won't result in a new snapshot of all articles
being produced.
HTH,
Paul Ibison (MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul,
I'm using Transactional Replication and it's Push subscription.
Can u plz give me the complete steps to add a single table
w/o starting snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
|||Naveed,
I don't have a complete script to hand, but this is the basic pattern I have
followed:
On the publisher:
EXEC sp_addarticle
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.source_owner = N'dbo',
@.source_object = N'Categories',
@.destination_table = N'Categories',
@.type = N'logbased',
@.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000000073,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_Categories',
@.del_cmd = N'CALL sp_MSdel_Categories',
@.upd_cmd = N'MCALL sp_MSupd_Categories'
GO
exec sp_addsubscription
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.subscriber = N'HOME-WIN2K',
@.destination_db = N'Pubs',
@.sync_type = N'none',
@.update_mode = N'read only'
GO
sp_refreshsubscriptions
run sp_scriptpublicationcustomprocs to generate the necessary procs and
apply them to the subscriber.
(BTW, I have also found that sp_refreshsubscription works for push and
pull).
HTH,
Paul Ibison

Adding new step to SQL Agent Job

I am attempting to add a new step to an existing sql server 2000 job. The
existing job starts upon SQL Server Agent startup. The steps consist of
running a stored procedure on several databases. The last step (4) is
simply a 'wait delay', then the steps begin again. When I insert a new step,
I receive a warning indicating that a circular reference is detected. I've
added steps to the same job in other databases, but did not receive the
warning. Is this something to be concerned about? Thanks in advance for any
suggestions.
For example,
Step 1: Process1
Step 2: Process2
Step 3: Process3
Step 4: Wait30seconds
If I attempt to insert a step between 1 and 2, the warning states:
"WARNING: Circular reference detected: Step [1] 'Process1 flows to Step[4]
'Wait30Seconds', and Step [4] 'Wait30Seconds' flows back to
Step[1]'Process1'. Are you sure that is what you want?
Thank you, Will.
"Will Alber" wrote:

> It sounds like this is exactly what you're wanting - as you want the steps
> to repeat (thus a circular reference is fine).
> This job will run forever, that's what it's warning you about.
> "danid12" <danid12@.discussions.microsoft.com> wrote in message
> news:15E030EF-B183-42B7-BCCA-53F0C456EBCB@.microsoft.com...
>
>

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent','true'
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||tks i'll try out what you have advised
Hilary Cotter wrote:[vbcol=seagreen]
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent','true'
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent',
'true'
--
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||tks i'll try out what you have advised
Hilary Cotter wrote:[vbcol=seagreen]
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent',
'true'
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent','true'
--
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
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||tks i'll try out what you have advised
Hilary Cotter wrote:
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent','true'
>> Hi,
>[quoted text clipped - 12 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

Adding a table to transactional repliaction

I am adding a table to an existing publication that has one pull subscription
using the steps listed below. When I run the snapshot agent, it does not
recognize the newly added article ("no subscriptions needed initialization").
I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and readd
the subscriber or subscription? If so, please let me know the steps.
ALSO, the steps below did work on a QA server but failed in production.
-- Thanks for your help.
exec sp_addarticle @.publication = N'my_PUBLICATION', @.article = N'my_TABLE',
@.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd = N'MCALL
sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false', @.force_invalidate_snapshot = 1
GO
select * from distribution..msArticles where article = 'my_TABLE'
go
EXEC sp_refreshsubscriptions
N'my_PUBLICATION'
go
exec sp_reinitsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.for_schema_change = 1,
@.subscriber = 'all'
GO
exec sp_addsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.subscriber = N'SUB_SERVER',
@.destination_db = N'pub_sub_database',
@.sync_type = N'automatic',
@.update_mode = N'read only'
I think you need to run sp_refreshpublications to get it to pick up the
changes.
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
"sbawa" <sbawa@.discussions.microsoft.com> wrote in message
news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>I am adding a table to an existing publication that has one pull
>subscription
> using the steps listed below. When I run the snapshot agent, it does not
> recognize the newly added article ("no subscriptions needed
> initialization").
>
> I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and
> readd
> the subscriber or subscription? If so, please let me know the steps.
> ALSO, the steps below did work on a QA server but failed in production.
> -- Thanks for your help.
>
> exec sp_addarticle @.publication = N'my_PUBLICATION', @.article =
> N'my_TABLE',
> @.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
> N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
> null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
> @.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
> sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd =
> N'MCALL
> sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null,
> @.auto_identity_range
> = N'false', @.force_invalidate_snapshot = 1
> GO
> select * from distribution..msArticles where article = 'my_TABLE'
> go
> EXEC sp_refreshsubscriptions
> N'my_PUBLICATION'
> go
> exec sp_reinitsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.for_schema_change = 1,
> @.subscriber = 'all'
> GO
> exec sp_addsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.subscriber = N'SUB_SERVER',
> @.destination_db = N'pub_sub_database',
> @.sync_type = N'automatic',
> @.update_mode = N'read only'
>
|||I tried using object search but unable to find this procedure in any of the
databases. Please help.
Your book on transactional replication is great. But you can probably see
that I haven't gotten too far into it yet.
"Hilary Cotter" wrote:

> I think you need to run sp_refreshpublications to get it to pick up the
> changes.
> --
> 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
> "sbawa" <sbawa@.discussions.microsoft.com> wrote in message
> news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>
>