how to auto restart the merge agent

I have a merge replication, the merge agent job failed with:
step 2 :
could not retrieve generation information at the 'Subscriber'. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed.
step 3:
The merge agent job failed. The merge process could not retrieve generation
information at the 'Subscriber'. NOTE: The step was retried the requested
number of times (10) without succeeding. The step failed. [SQLSTATE 42000]
(Error 14151). The step failed.
I am not sure what had caused the merge agent to fail but after I restarted
the merge agent , it automatically caught up. I would like to know if there
is a way to automatically re-start the merge agent after the system restored.
Thanks in advance for any help.
Wen Chang
Wen,
I'm not too sure what you mean by setting it off when the system gets
restarted. If you mean have it keep trying, then you could loop the steps in
the merge agent's job. Alternatively you could have it run on a schedule and
not continuously - eg once each minute.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to Assign Identity Key values in Replication

This is a very basic question on replication.

I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.

Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.

Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-

1. I don't want to use GUID.

2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.

i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.

any help will be highly appreciable.

You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||

thanks for your reply.

i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..

is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.

How to add the Column in Table which is in replication

Dear Friends
I have setup a merge replicaion between my two servers
and now i want to add some column in one table kindly
suggest how i can do the same without stoping the
replicaiton.
your earlier reply will guide me to solve the problem.
Best regards
Sharad
Sharad,
have a look in BOL for the command sp_repladdcolumn.
HTH,
Paul Ibison
|||Dear Fiend
Getting the following error.
Server: Msg 8114, Level 16, State 4, Procedure
sp_repladdcolumn, Line 0
Error converting data type nvarchar to bit.
for the code
sp_repladdcolumn @.source_object = 'OTHALLOCATION'
, @.column = 'CCICAPPNUM'
, @.typetext = 'NVARCHAR(50)'
, @.publication_to_add = 'GEMREPLGNETINFOTECH'
, @.schema_change_script = ''
, @.force_invalidate_snapshot =
force_invalidate_snapshot
, @.force_reinit_subscription =
force_reinit_subscription
Please suggest.
Best regards
Shailesh

>--Original Message--
>Sharad,
>have a look in BOL for the command sp_repladdcolumn.
>HTH,
>Paul Ibison
>
>.
>
|||Shailesh,
please can you try this script instead:
sp_repladdcolumn @.source_object = 'OTHALLOCATION'
, @.column = 'CCICAPPNUM'
, @.typetext = 'NVARCHAR(50) NULL'
, @.publication_to_add = 'GEMREPLGNETINFOTECH'
HTH,
Paul Ibison

How to add tabls to published article without recreating snapshot?

Hi experts:

We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?

By that is not possilbe, can we publish one new article on the same database?

Thanks in advance!

Ron

Hi Ron,

If you are using merge replication, you have to regenerate the snapshot for the entire publication after you added the new article. For snapshot\transactional replication, the snapshot agent will only generate the snapshot for the new article if the immediate_sync property of your publication is set to 0. In any case, you can always put the new article in a separate publication instead.

Hope that helps,

-Raymond

how to add table and all its index for replication

If you're talking about code as in stored procedure
calls, the stored procedures you need are:
exec sp_addarticle
exec sp_addsubscription or exec sp_refreshsubscriptions
I can script mine out for you and post them up, but it
would be more useful for you to create a dummy
publication and add the new articles using enterprise
manager. After they're created, right click the
publication and get EM to script out the publications.
Selecting the relevant sp_addarticle sp_and
addsubscription bits will give you what you need.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I guess i can try it out.. Are indexes added thru sp_addarticle ? or is
there a parameter in there that states to includes indexes,
constraints,etc..?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0faf01c4f6f9$292fbae0$a501280a@.phx.gbl...
> If you're talking about code as in stored procedure
> calls, the stored procedures you need are:
> exec sp_addarticle
> exec sp_addsubscription or exec sp_refreshsubscriptions
> I can script mine out for you and post them up, but it
> would be more useful for you to create a dummy
> publication and add the new articles using enterprise
> manager. After they're created, right click the
> publication and get EM to script out the publications.
> Selecting the relevant sp_addarticle sp_and
> addsubscription bits will give you what you need.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Have a look at @.schema_option in sp_addarticle. If you
select teh elipsis button in EM and on the article
properties, snapshot tab, you'll see the corresponding
checkboxes. The easiest thing to do is to select what you
want graphically, and then have EM do the bitmask logic
to generate the correct parameter value.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to add new column in MERGE replication

I am using MERGE replication.
Subscription is done @. 17 branch offices @. various locations. It running
successfuly.
Now I want to modify the table and want to add new column to the customer
table.
What should I do. Any changes made @. publisher side, how to apply @.
subscribers side.
Best Regards
Sanjay
Sanjay - please check out sp_repladdcolumn in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

How to add new article to publication?

Hi,
I'm use MSSQL2000 SP3 with replication.
I want to know that Can i add new article to existing publication by
Enterprice Manager ?
someone told me that we can do that in older version but not now,right? :confused:In UI you have to rebuild your replication/subscription from scratch. You can however use sp_addarticle/sp_addmergearticle (depending on your replication type).

how to add new article to existing publication without reinitialization

hi,
I am working on sql server 2000 replication. I have an
existing publication from server1 to server2 which has 6
tables in the publication. Now I want to add two more
tables into this publication.
How can I do it without have to re-initialize it?
is it always better to re-initialize the replication every
time you add new articles to the publication?
many thanks
JJ
1) use a separate publication is there are no relationships between the new
articles and articles in the existing publication.
2) think about using sp_addarticle. When you use this only the new
article(s) is "reinitialized", the existing publication is not regenerated.
Here is an example:
sp_addarticle 'pubs','jobs', 'jobs'
go
sp_refreshsubscriptions 'pubs'
pubs is my publication jobs is my article name and jobs is the source table.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:295d201c4654a$b05bfba0$a501280a@.phx.gbl...
> hi,
> I am working on sql server 2000 replication. I have an
> existing publication from server1 to server2 which has 6
> tables in the publication. Now I want to add two more
> tables into this publication.
> How can I do it without have to re-initialize it?
> is it always better to re-initialize the replication every
> time you add new articles to the publication?
> many thanks
> JJ
>
|||wow, I ought to try it out! thanks a lot Hilary.
JJ
>--Original Message--
> 1) use a separate publication is there are no
relationships between the new
>articles and articles in the existing publication.
>2) think about using sp_addarticle. When you use this
only the new
>article(s) is "reinitialized", the existing publication
is not regenerated.
>Here is an example:
>sp_addarticle 'pubs','jobs', 'jobs'
>go
>sp_refreshsubscriptions 'pubs'
>pubs is my publication jobs is my article name and jobs
is the source table.
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:295d201c4654a$b05bfba0$a501280a@.phx.gbl...
every
>
>.
>
|||hi Hilary,
Is the sp_addarticle the same thing when I do it through
the replication wizard (click on the publication property
and add more article in the 'article' tab)? if I do in
the wizard, will still only the new articles get re-
initialized?
JJ
>--Original Message--
> 1) use a separate publication is there are no
relationships between the new
>articles and articles in the existing publication.
>2) think about using sp_addarticle. When you use this
only the new
>article(s) is "reinitialized", the existing publication
is not regenerated.
>Here is an example:
>sp_addarticle 'pubs','jobs', 'jobs'
>go
>sp_refreshsubscriptions 'pubs'
>pubs is my publication jobs is my article name and jobs
is the source table.
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:295d201c4654a$b05bfba0$a501280a@.phx.gbl...
every
>
>.
>
|||Yes it is the same thing. However, thru EM, there is no
way to refresh only new articles in the subsriptions.
When you click on reintialize all subscriptions, it
reinitializes everything. At that time, you should use
SPRC sp_refreshsubscriptions. However, I haven';t seen
this proc working with PUSH subscription though. It only
works with PULL Subscriptions.

>--Original Message--
>hi Hilary,
>Is the sp_addarticle the same thing when I do it through
>the replication wizard (click on the publication
property[vbcol=seagreen]
>and add more article in the 'article' tab)? if I do in
>the wizard, will still only the new articles get re-
>initialized?
>JJ
>relationships between the new
>only the new
>is not regenerated.
>is the source table.
in[vbcol=seagreen]
>message
an[vbcol=seagreen]
has 6
>every
>.
>

How to add more tables to publishing database in transactional replication

I have setup the transactional replication with one table which is
working fine. I need more tables to publishing database in order to
test it. How to do this?
Thanks in advance
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sp_addarticle. You can also right click on the publication, select
publication properties, click on the articles tab, and then select show
unpublished objects, and then start to add more articles.
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
"Lance kepart" <lance4848@.yahoo.com> wrote in message
news:exxUNUsGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> I have setup the transactional replication with one table which is
> working fine. I need more tables to publishing database in order to
> test it. How to do this?
> Thanks in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to add field to table after replication has started?

I already have Merge replication started between 2
machines and now I want to add a field to a table that is
being used by replication. How can I go about doing this?
Is there a stored procedure to do this?
Thanks in advance,
Jim
Hi,
If you right-click on the publication you can access to all the articles to
alter them, if you go to the filer columns tab you will find the columns
list, you can alter there.
cheers
Salva
"Jim" wrote:

> I already have Merge replication started between 2
> machines and now I want to add a field to a table that is
> being used by replication. How can I go about doing this?
> Is there a stored procedure to do this?
> Thanks in advance,
> Jim
>
|||you can also use sp_repladdcolumn
SQL Server 2005 will allow DDL statements to be replicated.
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
"Jim" <jrathmann@.vtti.vt.edu> wrote in message
news:043101c539df$f3e70120$a601280a@.phx.gbl...
> I already have Merge replication started between 2
> machines and now I want to add a field to a table that is
> being used by replication. How can I go about doing this?
> Is there a stored procedure to do this?
> Thanks in advance,
> Jim

How to Add a Missing Index to a Table in Merge Replication?

One of the table that is in a merge replication somehow is missing an
index. Strangely, only the table in one of the subscriber of the merge
replication is missing the index; another subscriber and the publisher
of the merge replication don't have this problem.

How should I add the missing index back to that table? My understanding
is that making structural change on a table that is in
merge-replication is different from making change on a table that is
not merge-replicated. For example, when we need to add a column into a
table that is being merge-replicated, we must add the column by
changing the attributes in the properties of the published article
(table) instead of simply using CREATE INDEX command. I am wondering
whether there is a similar restriction on adding an index onto a table
that is merge replicated.

I have already added the index back to the table anyway. I am asking
here just in case doing this may get me into a problem later on.
Thanks in advance for any info.

Jay Chan<jaykchan@.hotmail.com> wrote in message
news:1106577312.674228.283880@.z14g2000cwz.googlegr oups.com...
> One of the table that is in a merge replication somehow is missing an
> index. Strangely, only the table in one of the subscriber of the merge
> replication is missing the index; another subscriber and the publisher
> of the merge replication don't have this problem.
> How should I add the missing index back to that table? My understanding
> is that making structural change on a table that is in
> merge-replication is different from making change on a table that is
> not merge-replicated. For example, when we need to add a column into a
> table that is being merge-replicated, we must add the column by
> changing the attributes in the properties of the published article
> (table) instead of simply using CREATE INDEX command. I am wondering
> whether there is a similar restriction on adding an index onto a table
> that is merge replicated.
> I have already added the index back to the table anyway. I am asking
> here just in case doing this may get me into a problem later on.
> Thanks in advance for any info.
> Jay Chan

I seem to remember that MSSQL only replicates indexes as part of an initial
snapshot, and after that they have to be maintained manually, so what you've
done would be fine. But I must admit that I'm not at all sure about the
details, so you might want to post in microsoft.public.sqlserver.replication
and see if you get a better answer.

Simon|||> I seem to remember that MSSQL only replicates indexes as part of an
initial
> snapshot, and after that they have to be maintained manually, so what
you've
> done would be fine. But I must admit that I'm not at all sure about
the
> details, so you might want to post in
microsoft.public.sqlserver.replication
> and see if you get a better answer.

Thanks for the confirmation. In any case, I cannot find a way to add an
index through the attributes of the replication publication anyway. I
am sure I am on the right track.

Jay Chan|||> I seem to remember that MSSQL only replicates indexes as part of an
initial
> snapshot, and after that they have to be maintained manually, so what
you've
> done would be fine. But I must admit that I'm not at all sure about
the
> details, so you might want to post in
microsoft.public.sqlserver.replication
> and see if you get a better answer.

Thanks for the confirmation. In any case, I cannot find a way to add an
index through the attributes of the replication publication anyway. I
am sure I am on the right track.

Jay Chan