ABNORMAL Transactional Replication behaviour

Actually i have set up a transactional replication with one publisher
and 1 subscriber (planning, then, to add other 6 subscribers later on).
The point is that after setting everything up, the replication works
really fine for some minutes and then just stops without any warning or
error!! The system is composed by this replication and some others
applications that writes on some tables on the subscriber. This app
starts writting data... everything works fine and then, BANG!, without
any reason data is not sent to the publisher anymore! SQL server doesn't
seems to notice that!
The replication has itself some filters and some tables have numeric
counters with identity YES (also the one that is written by the
application i mentioned before) . But, when i set up the publication i
assign a range to this counters and the replication blocks itself before
reaching the 1 % of the range i assing to this field!!
Anyone has an idea of what is happening?
Please... somebody help me!!
F.P.
what does sp_browsereplcmds reveal? Run this in the distribution database to
see if commands are pooling there.
If they aren't run sp_repltrans in your publication database.
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
"newsgroup" <fabio.pliger@.siavr.it> wrote in message
news:nVn4f.10530$65.287427@.twister1.libero.it...
> Actually i have set up a transactional replication with one publisher and
> 1 subscriber (planning, then, to add other 6 subscribers later on). The
> point is that after setting everything up, the replication works really
> fine for some minutes and then just stops without any warning or error!!
> The system is composed by this replication and some others applications
> that writes on some tables on the subscriber. This app starts writting
> data... everything works fine and then, BANG!, without any reason data is
> not sent to the publisher anymore! SQL server doesn't seems to notice
> that!
> The replication has itself some filters and some tables have numeric
> counters with identity YES (also the one that is written by the
> application i mentioned before) . But, when i set up the publication i
> assign a range to this counters and the replication blocks itself before
> reaching the 1 % of the range i assing to this field!!
> Anyone has an idea of what is happening?
> Please... somebody help me!!
> F.P.
|||Thanks Hilary for the advice, but sp_browsereplcmds reveals only that
the last command was the one for the last record send from the
subscriber to the published database(no cmds for the records in the
subscriber and not replicated to the publisher). Then i tried to run
sp_repltrans on my publication db but it returns me no results... All
the agents continue "working" without any error, but no more records are
replicated (neither from pubisher to subscriber or vice-versa). Wherelse
can i search for the transactions failed? I mean...
Any other idea? The main thing that upsets me is that i'm not able to
see what's going wrong!! I'll try changing some configurations... I'm
using a push subscription... is it better to use a pull one?
I really need some help...
thanks...
Hilary Cotter ha scritto:
> what does ? Run this in the distribution database to
> see if commands are pooling there.
> If they aren't run sp_repltrans in your publication database.
>

A simple question regarding SQL 2000 Transactional Replication

We have a database that we would like to replicate to another server.
Am i correct to asume that the database that is updated would be the
publisher and that the subscriber database would be the replicated copy?
Greg, in the case of plain transactional replication, yes, this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

A lil confused here from BOL

From BOL : How Transactional Replication Works
"After all Subscribers have received transactions, the Distribution Cleanup
Agent removes delivered transactions in the distribution database. Delivered
transactions are kept in the distribution database for a defined period
known as the retention period. Setting a retention period while scheduling
backups can ensure that information required to recover a destination
database automatically is available within the distribution database.
For example, if a Subscriber has scheduled a transaction log dump of a
destination database every 24 hours, you could set the retention period to
48 hours. Even if the Subscriber experiences a failure immediately before a
scheduled backup, all transactions necessary to restore the replicated
tables automatically will still be available to the distribution process of
the Distributor."
So im confused at the first 2 statements where it says that the cleanup
agent job removes the delivered transaction in the distrib database but then
it also states that those delivered transactions are kept in the distrib
database for a defined period and then gives an example that we could set
the retention period to 48 hrs.. So if the cleanup agent cleans them, how is
it retained ?
Hassan,
they're retained until all subscribers have subscribed or the retention
period is reached, whichever is shorter. In the case of anonymous
subscribers this changes and they're retained until the end of the retention
period even if subscribers have picked them up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

A Guide to SQL Server Transactional and Snapshot Replication from Hilary Cotter

Hi all
Can tell me anyone, if the book
A Guide to SQL Server Transactional and Snapshot Replication from
Hilary Cotter, Publish Date June 15/2004
are available?
How and where can I order this book.
http://www.nwsu.com/0974973602.html
Thanks Thomas
It hasn't gone to print yet. It should be going to print early next month.
Then it will take about 25-35 days to print. I'll announce when you can
preorder the book here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:41088b9f.6019281@.news.t-online.de...
> Hi all
> Can tell me anyone, if the book
> A Guide to SQL Server Transactional and Snapshot Replication from
> Hilary Cotter, Publish Date June 15/2004
> are available?
> How and where can I order this book.
> http://www.nwsu.com/0974973602.html
> Thanks Thomas
|||Hi Hillary,
thanks for fast reply. Currently I am working on an bidirectional
replication migration from 6.5 to 2k.
Additional information would be nice for me, but not absolute needed.
But 30 days are to late for me.
Is it possible to get an (beta) online issue?
Greetings from Germany
On Wed, 28 Jul 2004 08:12:58 -0400, "Hilary Cotter" <hilaryk@.att.net>
wrote:

>It hasn't gone to print yet. It should be going to print early next month.
>Then it will take about 25-35 days to print. I'll announce when you can
>preorder the book here.

A Guide to SQL Server Transactional and Snapshot Replication

Is it ready?
Last I heard it was at the printer, with a street date mid to late November.
It was stuck in typesetting for several months.
I'll let you know when I hear more.
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:4171e9f5.245848500@.news.t-online.de...
> Is it ready?

a group of sp_MSins stored procs has 2 members?

I decide to check with the forum because I haven't got any lucky result.
Here is the transactional replication between two SQL 2000 standard edition
+ sp3 servers I inherited a few month ago. On the subscriber, when I pushed
a new subcription there was an error "Cannot insert duplicate key row in
object bla bla with unique index 'bla bla'". Well, the errors are easy to
understand and fix. To my surprise, I noticed that the replication insert
stored proc has two stored procs defined in it. It looks like this:
create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
AS
BEGIN
insert into "smfPrice"(
"PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
"AuditInformationId", "IsoCurrencyCode", "DataSourceId"
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
)
END
GO
create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
as
if exists ( select * from "tablename"
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
)
begin
update "tablename" set "AdjustedPrice" = @.c4,"Price" =
@.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" = @.c8
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
end
else
begin
insert into "tablename" (
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
ormationId","IsoCurrencyCode","DataSourceId" ) values (
@.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
end
GO
I think the second version is better writen than the first one and can
suppress the said error. But obviously SQL server replication is not able to
pick the right one. When I swaped the procs titles while leaving the
definitions unchanged, the error went away.
My puzzle is: What event triggers the generation of the second set of procs?
What internal mechanism guarantees replication to use the correct version?
I'm thinking of removing the first instance of procs because the second one
already covers it. But for the future, I want to find out why it happens and
potentially come up with a better solution. I hope this is not a bug.
I'd like to hear your thoughs.
Richard
the second proc is a custom proc generated by a user. I suspect each proc
has a different owner. You should be able to check this by doing an sp_help
sp_MSins_tablename
or by browsing them in EM,
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
>I decide to check with the forum because I haven't got any lucky result.
> Here is the transactional replication between two SQL 2000 standard
> edition
> + sp3 servers I inherited a few month ago. On the subscriber, when I
> pushed
> a new subcription there was an error "Cannot insert duplicate key row in
> object bla bla with unique index 'bla bla'". Well, the errors are easy to
> understand and fix. To my surprise, I noticed that the replication insert
> stored proc has two stored procs defined in it. It looks like this:
> create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> AS
> BEGIN
> insert into "smfPrice"(
> "PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
> "AuditInformationId", "IsoCurrencyCode", "DataSourceId"
> )
> values (
> @.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
> )
> END
> GO
> create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> as
> if exists ( select * from "tablename"
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> )
> begin
> update "tablename" set "AdjustedPrice" = @.c4,"Price" =
> @.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" =
> @.c8
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> end
> else
> begin
> insert into "tablename" (
> "PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
> ormationId","IsoCurrencyCode","DataSourceId" ) values (
> @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
> end
> GO
> I think the second version is better writen than the first one and can
> suppress the said error. But obviously SQL server replication is not able
> to
> pick the right one. When I swaped the procs titles while leaving the
> definitions unchanged, the error went away.
> My puzzle is: What event triggers the generation of the second set of
> procs?
> What internal mechanism guarantees replication to use the correct version?
> I'm thinking of removing the first instance of procs because the second
> one
> already covers it. But for the future, I want to find out why it happens
> and
> potentially come up with a better solution. I hope this is not a bug.
> I'd like to hear your thoughs.
>
> Richard
>
>
|||These two procs are actually in one definition, except they have a suffix.
And they all belong to dbo.
I am able to reproduce what has happened by checking the "do not block"
option on and off on the snapshot tab in publicatio properties, I could see
two different version of procs are generated. I can also see the diference
is with the sp_addpublication @.sync_method variable. One is 'native' that
creates one sp_MSins proc; When I check the said option, SQL uses
'concurrent' and creates two procs in one set.
To clarify this, the two procs in one set means two "create proc" statements
in the GUI when you double click on the proc, one of which is tagged with
";2" as in sp_MSinstablename;2
99% of my publication are created this way and they have been running well
with no problems. That means SQL Server replicaiton knows when to use which
proc to do the job. My current error indicates that replication somehow lost
its judgement like it used to. It takes the default proc (the top one)
instead of taking the second (bottom one). So far, I've no luck in finding
out any useful hints.
Microsoft developers, possible to shed some light on this?
Richard
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
> the second proc is a custom proc generated by a user. I suspect each proc
> has a different owner. You should be able to check this by doing an
sp_help[vbcol=seagreen]
> sp_MSins_tablename
> or by browsing them in EM,
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
insert[vbcol=seagreen]
@.c3[vbcol=seagreen]
@.c3[vbcol=seagreen]
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
able[vbcol=seagreen]
version?
>
|||I've made a bit more progress:
It appears sp_script_reconciliation_insproc created sp_MSins;2.
sp_scriptinsproc created sp_MSins.
sp_depends shows nobody depends on sp_script_reconciliation_insproc.
Looks like two separate processes triggered each stored proc individually
with sp_scriptinsproc doing it first.
Is this a normal behavior?
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u9mlzW5tEHA.3200@.TK2MSFTNGP09.phx.gbl...
> These two procs are actually in one definition, except they have a suffix.
> And they all belong to dbo.
> I am able to reproduce what has happened by checking the "do not block"
> option on and off on the snapshot tab in publicatio properties, I could
see
> two different version of procs are generated. I can also see the diference
> is with the sp_addpublication @.sync_method variable. One is 'native' that
> creates one sp_MSins proc; When I check the said option, SQL uses
> 'concurrent' and creates two procs in one set.
> To clarify this, the two procs in one set means two "create proc"
statements
> in the GUI when you double click on the proc, one of which is tagged with
> ";2" as in sp_MSinstablename;2
> 99% of my publication are created this way and they have been running well
> with no problems. That means SQL Server replicaiton knows when to use
which
> proc to do the job. My current error indicates that replication somehow
lost[vbcol=seagreen]
> its judgement like it used to. It takes the default proc (the top one)
> instead of taking the second (bottom one). So far, I've no luck in finding
> out any useful hints.
> Microsoft developers, possible to shed some light on this?
>
> Richard
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
proc[vbcol=seagreen]
> sp_help
result.[vbcol=seagreen]
in[vbcol=seagreen]
> to
> insert
"Price",[vbcol=seagreen]
> @.c3
=
> @.c3
>
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
> able
> version?
second[vbcol=seagreen]
happens
>