Queued Updating Subscribers Question

When I looked into setting this up I received a message that an identity column would be added to all my tables for this type of replication.
Wouldn't this result in my having to change all code that touches these tables to take the new column into account?
Queued Updating is new to me, I am trying to learn the best replication option for our reporting database, but am a little confused.
Any/All help is appreciated!
Thanx!
JLS,
if the identity column is already there on the publisher, it is transferred
to the subscriber but no new identity columns will be created. On each
indetity column the column is designated as Identity Yes (Not for
Replication). This ensures that the replication process can insert values
into the column, and for an insert on the subscriber itself SQL Server can
have values allocated as per normal. To avoid clashes, identity ranges are
allocated to publisher and each subscriber, each node having different
seeds; these ranges and the allocating of new ranges is configurable at the
publication level.
HTH,
Paul Ibison
|||I'm sorry Paul, I don't follow. I have been setting up and tearing down
replication every which way from Sunday, so everything is sort of running
together.
I changed all my identity columns on the Subscriber to Yes(Not for
Replication), I don't really have any issue here. It is my understanding
that what happens here is the value from the Publisher is popped into this
field on the Subscriber, and that's the way I would want it to work as I
don't intend to have any updates occurring on the Subscriber.
When I selected Queued Updating as an option, in the Identity warning screen
I saw a new warning that stated a new column would be added, and every table
I am replicating was listed, and the new column is a replication column.
The warning also stated that this may cause INSERT to fail & cause the table
to become larger.
Can you explain this in "For Dummies who haven't had enough coffee yet this
morning" terms?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23dXpbRuTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> JLS,
> if the identity column is already there on the publisher, it is
transferred
> to the subscriber but no new identity columns will be created. On each
> indetity column the column is designated as Identity Yes (Not for
> Replication). This ensures that the replication process can insert values
> into the column, and for an insert on the subscriber itself SQL Server can
> have values allocated as per normal. To avoid clashes, identity ranges are
> allocated to publisher and each subscriber, each node having different
> seeds; these ranges and the allocating of new ranges is configurable at
the
> publication level.
> HTH,
> Paul Ibison
>
|||JLS,
the column you are referring to is not an identity column -
it is a GUID. This is added and may cause tsql to fail
when it doesn't have an explicit column list eg
insert into table1
select * from replicatetable
If you are using Queued Updating Subscribers and are
letting replication do the initialization for you, you
don't need to alter identity columns on the subscriber -
they'll be set correctly for you.
HTH,
Paul Ibison
ps if you don't intend having subscribers update the data,
then why not use standard transactional replication?
|||Ah, ok I get it now. Thanx!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1ae5301c44eee$b71dc120$a001280a@.phx.gbl...
> JLS,
> the column you are referring to is not an identity column -
> it is a GUID. This is added and may cause tsql to fail
> when it doesn't have an explicit column list eg
> insert into table1
> select * from replicatetable
> If you are using Queued Updating Subscribers and are
> letting replication do the initialization for you, you
> don't need to alter identity columns on the subscriber -
> they'll be set correctly for you.
> HTH,
> Paul Ibison
> ps if you don't intend having subscribers update the data,
> then why not use standard transactional replication?
>

queued updating error

We have setup transactional replication with queued updating between two
machines. both machines have the same sqlagent account with the same
password. When inserting records on the Publishers ( e. on the table) then
the data gets replicated fine and the subscriber gets updated with the insert
record. the problem is when we try to insert the records from the subscriber
then replication fails with login. What could possible be the problem here
because the account we are using is the same and has writes in both machines
the publisher and subscriber.
Mandla,
please take a look at this link :
http://support.microsoft.com/default...;en-us;Q320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Queued Updating

When i configured replication by default SQl Server (2000) use the Queued
Updating
what does it mean "changes queued at the subscribers until they can be
applied at the publusher" ?
Thanks
Message posted via http://www.droptable.com
somebody can help me on the topic?
Message posted via http://www.droptable.com
|||If you insert/update/delete a record at the subscriber it won't enter a 2PC
transaction like immediate updating subscribers, but will enter a queue
table (or MSMQ) on the subscriber. Only when the queue reader agent is run
will the commands be propagated to the publisher. So, things like identities
need more thinking about, as they'll be managed on the publisher and
subscriber, due to the increased autonomy of the subscribers.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks
Message posted via http://www.droptable.com

Queued update

Dear friends

I have a simple doubt.

1)What is the difference between Queued Updating & Immediate Updating in Transactional Relplication.

2)What is the difference between Meged Replication & Peer to Peer replication.Because in both each node will Act as Publisher/Subscriber.So load balancing is possible both type replication na?

3) how to replicate views,stored procedure & functions.Beacuse when applying intial snapshot the copy is getting in subscriber.But afterwards whatever changes occurring in view,procedure not propagating from publisher to subscriber.what to do in this case

Filson

1. Queue can handle offline changes, immediate updating works only when publisher/subscriber are connected, otherwise change at the subscriber will fail.

2. In a nutshell, P2P is used mostly for server-server environment, Merge is for server-client environment. Read BOL topics (cited below) for more information.

3. In sp_addpublication/sp_addmergepublication, set @.replicate_ddl = 1. For more information about schema changes, see "Making Schema Changes on Publication Databases", http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

For more information, please see Books Online topic "SQL Server Replication", http://msdn2.microsoft.com/en-us/library/ms151198.aspx.

queued updatable subscribers

I'm using transactional replication with queued updatable
subscribers.
My reason for doing this is that I want to be able to
update the subscribers in case they lose network
connection to the publisher.
Everything worked fine in the begining but as our product
has evolved the publication has grown to more than 520
articles. My problem is that the time it takes to update
a table at the subscriber has increased and I see a
relationship with the nuber of articles in the
publication.
Does anyone know if Microsoft has addressed this problem
in SQL server 2005 or is there a different way to solve
the problem. Logshipping doesn't help me and database
mirroring only handles one subscriber as far as I can
tell.
Peter,
queued updating subscribers is optimised only for few updates at the
subscriber. Where this is not the case you might want to consider using
merge replication which will also allow you the autonomy you require. I
agree that log shipping is not suitable as the subscriber database is
recovered in NORECOVERY or STANDBY mode which won't allow users to change
the data.
Regards,
Paul Ibison
(ps when you talk about database mirroring presumably you are referring to a
third party tool, or are you currently using SQL Server 2005 beta 1.)
|||Thanks for the answer Paul!
I made some tests with merge replication and it works better in the
failover scenario but the overhead I get in the normal scenario is maybe
to big. I'm going to study merge replication a bit more to see if there
is anything I can do to speed it up when I make updates at the
publisher.
(Yes database mirroring is the new feature in SQL 2005)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Peter,
these links may help you to optimize the merge agent for your needs:
Replication merge gaent parameters (esp pollinginterval)
http://msdn.microsoft.com/library/de...lmerg_4y2h.asp
Merge Replication: Performance Tuning and Optimization
http://www.microsoft.com/technet/pro.../mergperf.mspx
HTH,
Paul Ibison

Queued updatable subscriber...

can someone explain the proper usage of a queued updatable subscriber in a
transactional replication scheme? in other words, what are the ways in which
you can tell the subscriber to empty the queue of outstanding transactions
that are to be applied back at the publisher (keep in mind, although maybe
not relevent, no inserts occur at the subscriber, only udpates). is it just
as easy as scheduling the job that gets created for the queue reader agent?
thanks all!
Yes - it's just a job. You can also use sp_replqueuemonitor to read the
MSreplication_queue table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Queued Transaction Failing

Let me put this in english (very long day).
The my myfile_0.sql is my pre-snapshot script that drops
and recreates the tables.

>--Original Message--
>Hello,
>Can anyone help me with a Queued Transaction thats
failing?
>I just set this up to do a snapshot and queued
>tranactional rep. The snapshot works great the queued
part
>brings the error (last command)
>\\IMYSERVER\Replication\unc\MyDirectory\200405271 60853
>\myfile_0.sql.
>I've checked out myfile_0.sql in QA and it works, and the
>snapshot deletes and recreates the tables (thats the
>script it running).
>Thanks for looking
>Rose
>.
>
Rose,
sorry but I'm still not too clear on what you want. Is the script actually
being propagated correctly but you don't want to drop the table at the
destination? In this case the behaviour that you have is controlled by the
@.pre_creation_cmd in sp_addarticle. In the GUI this is available on the
elipsis button next to the article (table). The default option is to drop
the table, but you also have the choice to leave it unchanged, truncate it
or remove selected rows.
HTH,
Paul Ibison
|||Firstly my apologies,
Looking at it again my remark 'let me put this in
english', was not nameed at you but me, occasionally I
have a habit of puting things in without proper proof
reading.
The Reason I delete the tables is thats what was
recommended by a white paper for transactional
replication, so I thought I would try it here.
Anyway I think I might of gotten to the bottom of it. The
database was a Transaction Replication database -
Immediate before this, and what I think is happening is
that it still thinks it is, so its not allowing me to
delete.
Anyway thanks Paul, and why aren't you a MVP ?
Rose

>--Original Message--
>Rose,
>sorry but I'm still not too clear on what you want. Is
the script actually
>being propagated correctly but you don't want to drop the
table at the
>destination? In this case the behaviour that you have is
controlled by the
>@.pre_creation_cmd in sp_addarticle. In the GUI this is
available on the
>elipsis button next to the article (table). The default
option is to drop
>the table, but you also have the choice to leave it
unchanged, truncate it
>or remove selected rows.
>HTH,
>Paul Ibison
>
>.
>
|||Rose,
you can use sp_removedbreplication on the subscriber before subscribing to
remove any traces of replication, or sp_MSunmarkreplinfo on the offending
table.
Thanks for your comment - MVP status would be extremely welcome but anyway
the way I look at it is that as I train the MS course on replication
(www.pygmalion.com) answering questions is still a good way of keeping on
top of things.
Cheers,
Paul

Queued Transaction Failing

Hello,
Can anyone help me with a Queued Transaction thats failing?
I just set this up to do a snapshot and queued
tranactional rep. The snapshot works great the queued part
brings the error (last command)
\\IMYSERVER\Replication\unc\MyDirectory\2004052716 0853
\myfile_0.sql.
I've checked out myfile_0.sql in QA and it works, and the
snapshot deletes and recreates the tables (thats the
script it running).
Thanks for looking
Rose
Rose,
part of your error message is missing - please can you post up the complete
one.
I'm assuming that the missing bit talks about not being able to read the
snapshot file and that this error is produced by the distribution agent. If
this is the case then can you check that the account being used by the
distribution agent (sql server agent) has read rights on the share you're
using.
Regards,
Paul Ibison
|||Thanks for you replay Paul,
Thats all the error message I can get out of it. I have
right clicked on the Subscription, looked in error detail
and that was all that appeared.
I don't think the problem is it can't read it, as I have
some Transactional Reps pointing to the same directory.
Could it be becasue of the table changes the Queued
replication is suppose to make ?

>--Original Message--
>Rose,
>part of your error message is missing - please can you
post up the complete
>one.
>I'm assuming that the missing bit talks about not being
able to read the
>snapshot file and that this error is produced by the
distribution agent. If
>this is the case then can you check that the account
being used by the
>distribution agent (sql server agent) has read rights on
the share you're
>using.
>Regards,
>Paul Ibison
>
>.
>

QUEUE READER FAILS! Help...

Hi,
We are using transactional replication with queued updating between
SQL2000 Servers (Win 2003).
1) The queue reader has failed with the error message "Queue Reader
aborting. The step failed."
The error details stack shows (from last to first):
Queue Reader aborting. The step failed
Processed 53 queued trans, 105 cmds, 1 conflicts
Failed while applying queued message to publisher
CQueueRdrRowChange: ApplyCommand: Failed to create instance of
pMessage->pParams(ProcParams)
What's causing this? How can I restart the Queue Reader Agent after the
failure?
thanks,
Fabio
I take it that the error repeats next time you run the queue reader. To
restart it after failure I would put it on a schedule of every 5 minutes.
You can have it return to job step 1 on failure.
How many subscribers do you have? Also queued is designed for less than 10
subscribers and where the majority of the DML occurs on the publisher - does
your topology fit into this case?
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
<fabio.pliger@.gmail.com> wrote in message
news:1164187151.581038.182820@.j44g2000cwa.googlegr oups.com...
> Hi,
> We are using transactional replication with queued updating between
> SQL2000 Servers (Win 2003).
> 1) The queue reader has failed with the error message "Queue Reader
> aborting. The step failed."
> The error details stack shows (from last to first):
> Queue Reader aborting. The step failed
> Processed 53 queued trans, 105 cmds, 1 conflicts
> Failed while applying queued message to publisher
> CQueueRdrRowChange: ApplyCommand: Failed to create instance of
> pMessage->pParams(ProcParams)
> What's causing this? How can I restart the Queue Reader Agent after the
> failure?
> thanks,
> Fabio
>
|||Yes, the error repeates everytime i re-run the queue reader.. and it
always returns me those actions:
- Queue reader aborting...
- Processing ## queued trans, ## cmds, ## conflicts
- Failed whiale applying queued message to publisher
- CQueuedRdrRowChange::ApplyCommand: Failed.... bla bla...
- Queue Reader Agent blabla Started
- Starting agent
I do have 5 subscribers. 4 of those have a filter by column on only one
table.
Much DML occurs also at the subscribers...
I see that after the agentes fails... data from 2 of the subscribers is
sent to the publisher (and their Ms_replication_queue table il empty,
but no data from the others subscribers is sent to the publisher and
their replication queue table is unchanged... Can help if i delete all
the records in those tables at the subscribers?
What kind of error is that? Any hint on what i can look at to solve it?
thanks,
Fabio
Hilary Cotter ha scritto:
[vbcol=seagreen]
> I take it that the error repeats next time you run the queue reader. To
> restart it after failure I would put it on a schedule of every 5 minutes.
> You can have it return to job step 1 on failure.
> How many subscribers do you have? Also queued is designed for less than 10
> subscribers and where the majority of the DML occurs on the publisher - does
> your topology fit into this case?
> --
> 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
>
> <fabio.pliger@.gmail.com> wrote in message
> news:1164187151.581038.182820@.j44g2000cwa.googlegr oups.com...
|||Can you enable logging? Follow these steps
http://support.microsoft.com/default.aspx?scid=kb;en-us;312292&sd=tech
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
<fabio.pliger@.gmail.com> wrote in message
news:1164207423.995497.310220@.h48g2000cwc.googlegr oups.com...
> Yes, the error repeates everytime i re-run the queue reader.. and it
> always returns me those actions:
> - Queue reader aborting...
> - Processing ## queued trans, ## cmds, ## conflicts
> - Failed whiale applying queued message to publisher
> - CQueuedRdrRowChange::ApplyCommand: Failed.... bla bla...
> - Queue Reader Agent blabla Started
> - Starting agent
> I do have 5 subscribers. 4 of those have a filter by column on only one
> table.
> Much DML occurs also at the subscribers...
> I see that after the agentes fails... data from 2 of the subscribers is
> sent to the publisher (and their Ms_replication_queue table il empty,
> but no data from the others subscribers is sent to the publisher and
> their replication queue table is unchanged... Can help if i delete all
> the records in those tables at the subscribers?
> What kind of error is that? Any hint on what i can look at to solve it?
> thanks,
> Fabio
>
> Hilary Cotter ha scritto:
>
|||Here's what i get:
Connecting to QueueReader 'WN055.distribution'
Queue Reader Agent [WN055].8 (Id = 5) started
[11/22/2006 4:32:11 PM]WN055.distribution: execute
dbo.sp_MShelp_profile 5, 9, N''
Connecting to WN046 'WN046.FDF'
[11/22/2006 4:32:11 PM]WN055.distribution: exec
dbo.sp_helpdistpublisher @.publisher = N'WN055'
Connecting to WN055 'WN055.FDF'
SQL Command : <exec [dbo].[sp_MSsync_ins_Aut_LogBatch_3] N'WN046',
N'FDF', 250008816, 250000361, N'AUTO', '2006-11-22 16:19:32.000',
N'34B4', '5992D20D-A9E7-40AF-AB71-43F650E4E8AC', 1>
Disconnecting from WN055 'WN055'
Disconnecting from WN046 'WN046'
Connecting to WN030 'WN030.FDF'
[11/22/2006 4:32:26 PM]WN055.distribution: exec
dbo.sp_helpdistpublisher @.publisher = N'WN055'
Connecting to WN055 'WN055.FDF'
CQueueRdrRowChange::ApplyCommand:Failed to create instance of
pMessage->pParams(ProcParams)
Failed while applying queued message to publisher
Disconnecting from WN055 'WN055'
Worker Thread 540 : Task Failed
Disconnecting from WN030 'WN030'
Processed 29 queued trans, 57 cmds, 0 conflicts
Queue Reader aborting
It seem's like the subscriber WN030has some problems comunicating with
publisher-... right? Any hint?
Fabio
Hilary Cotter ha scritto:
[vbcol=seagreen]
> Can you enable logging? Follow these steps
> http://support.microsoft.com/default.aspx?scid=kb;en-us;312292&sd=tech
> --
> 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
>
> <fabio.pliger@.gmail.com> wrote in message
> news:1164207423.995497.310220@.h48g2000cwc.googlegr oups.com...
|||I've shutedown the sqlserveragent at the subscriber WN030 (the one
generating the error...) and the queue reader agent don't fails
anymore... So.. bypassing this subscriber the agent is able to run..
but my goal is to solve the problem and reconnect the subscriber...
Deleting the MS_replicationqueue at tje subscriber can help? I would
prefer to don't loose those transactions... but if it's the only way i
can delete it...
thanks,
Fabio
fabio.pliger@.gmail.com ha scritto:
[vbcol=seagreen]
> Here's what i get:
> Connecting to QueueReader 'WN055.distribution'
> Queue Reader Agent [WN055].8 (Id = 5) started
> [11/22/2006 4:32:11 PM]WN055.distribution: execute
> dbo.sp_MShelp_profile 5, 9, N''
> Connecting to WN046 'WN046.FDF'
> [11/22/2006 4:32:11 PM]WN055.distribution: exec
> dbo.sp_helpdistpublisher @.publisher = N'WN055'
> Connecting to WN055 'WN055.FDF'
> SQL Command : <exec [dbo].[sp_MSsync_ins_Aut_LogBatch_3] N'WN046',
> N'FDF', 250008816, 250000361, N'AUTO', '2006-11-22 16:19:32.000',
> N'34B4', '5992D20D-A9E7-40AF-AB71-43F650E4E8AC', 1>
> Disconnecting from WN055 'WN05
> Disconnecting from WN046 'WN046'
> Connecting to WN030 'WN030.FDF'
> [11/22/2006 4:32:26 PM]WN055.distribution: exec
> dbo.sp_helpdistpublisher @.publisher = N'WN055'
> Connecting to WN055 'WN055.FDF'
> CQueueRdrRowChange::ApplyCommand:Failed to create instance of
> pMessage->pParams(ProcParams)
> Failed while applying queued message to publisher
> Disconnecting from WN055 'WN055'
> Worker Thread 540 : Task Failed
> Disconnecting from WN030 'WN030'
> Processed 29 queued trans, 57 cmds, 0 conflicts
> Queue Reader aborting
>
> It seem's like the subscriber WN030has some problems comunicating with
> publisher-... right? Any hint?
> Fabio
>
>
> Hilary Cotter ha scritto:
|||No, it looks like it is crashing on a single command. Almost like there is a
command in the queue causing it to crash.
Deleting the contents of the queue will cause you to lose work. I would stop
all users working on the subscriber database and try to flush the queue and
see if there is a command stuck in there.
keep logging, and run the queue until one command remains and then I would
consider deleting that one command.
Something is very wrong here. I think you should open a support incident
with Microsoft on this one. Where is Raymond when you really want him?
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
<fabio.pliger@.gmail.com> wrote in message
news:1164210515.241841.233780@.f16g2000cwb.googlegr oups.com...
> Here's what i get:
> Connecting to QueueReader 'WN055.distribution'
> Queue Reader Agent [WN055].8 (Id = 5) started
> [11/22/2006 4:32:11 PM]WN055.distribution: execute
> dbo.sp_MShelp_profile 5, 9, N''
> Connecting to WN046 'WN046.FDF'
> [11/22/2006 4:32:11 PM]WN055.distribution: exec
> dbo.sp_helpdistpublisher @.publisher = N'WN055'
> Connecting to WN055 'WN055.FDF'
> SQL Command : <exec [dbo].[sp_MSsync_ins_Aut_LogBatch_3] N'WN046',
> N'FDF', 250008816, 250000361, N'AUTO', '2006-11-22 16:19:32.000',
> N'34B4', '5992D20D-A9E7-40AF-AB71-43F650E4E8AC', 1>
> Disconnecting from WN055 'WN055'
> Disconnecting from WN046 'WN046'
> Connecting to WN030 'WN030.FDF'
> [11/22/2006 4:32:26 PM]WN055.distribution: exec
> dbo.sp_helpdistpublisher @.publisher = N'WN055'
> Connecting to WN055 'WN055.FDF'
> CQueueRdrRowChange::ApplyCommand:Failed to create instance of
> pMessage->pParams(ProcParams)
> Failed while applying queued message to publisher
> Disconnecting from WN055 'WN055'
> Worker Thread 540 : Task Failed
> Disconnecting from WN030 'WN030'
> Processed 29 queued trans, 57 cmds, 0 conflicts
> Queue Reader aborting
>
> It seem's like the subscriber WN030has some problems comunicating with
> publisher-... right? Any hint?
> Fabio
>
>
> Hilary Cotter ha scritto:
>
|||Sorry for my bad english.. but what do you mean by flushing the
queue? comand queue at the distributor or the queue table at the
subscriber? Do u mean i should delete records one by one?
Hilary Cotter ha scritto:

> No, it looks like it is crashing on a single command. Almost like there is a
> command in the queue causing it to crash.
> Deleting the contents of the queue will cause you to lose work. I would stop
> all users working on the subscriber database and try to flush the queue and
> see if there is a command stuck in there.
> keep logging, and run the queue until one command remains and then I would
> consider deleting that one command.
> Something is very wrong here. I think you should open a support incident
> with Microsoft on this one. Where is Raymond when you really want him?
> --
> 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
>
>

Queue Reader fails occasionally

I am using transactional replication with queued updating between two servers
running SQL Server 2000 SP3. Publisher and distributor run both on the same
server (a cluster machine). There is one subscriber. The Queue Reader Agent
starts successfully, and transactions originating on either side are properly
replicated. However, the Queue Reader occasionally fails. I enabled logging,
but the output file looks normal to me; several queries for queued data, but
then it seems to timeout. It just sits there for 3 minutes, then fails and
retries. No error message in the output file. Finally (after specified count
of retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I have to
examine the queue (table MSreplication_queue) and delete the first
respectively first few rows. Before deleting I execute the corresponding
queries (sp calls of type sp_MSsync_upd_MyTable) in Query Analyzer, what
succeeds. After deleting the rows from MSreplication_queue I can restart
Queue Reader Agent and now all works properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
What if you run these commands manually from the subscriber using QA? What
I'm thinking is that it might be a locking (blocking) issue which you'd be
able to determine using sp_who2 at the publisher.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not sure whether I did understand your reply correctly. What I usually do
to solve the problem is running these commands manually at the publisher
using QA. (I simply copy them from the output file.). This functions properly,
I can even track this by querying the correspondent rows at the subscriber
and at the publisher. Before executing the commands in QA, the changes are
only at the subscriber, afterwards they are at both the subscriber and the
publisher. That's why I think it cannot be a locking issue.
Message posted via http://www.droptable.com
|||If it's definitely not blocking then I'm not too sure. You could use
profiler on the publisher to check the commands are getting that far when
running the queue reader agent. You could also simultaneously run profiler
on teh subscriber to confirm the queue is being read.
Rgds,
Paul Ibison, SQL Server MVP
|||OK, I think you could be right. I'll try then with profiler...
Thanks, Dorrit
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200507/1

Queue Reader failes occasionally

I am using transactional replication with queued updating between two
servers running SQL Server 2000 SP3. Publisher and distributor run
both on the same server (a cluster machine). There is one subscriber.
The Queue Reader Agent starts successfully, and transactions
originating on either side are properly replicated. However, the Queue
Reader occasionally fails. I enabled logging, but the output file
looks normal to me; several queries for queued data, but then it seems
to timeout. It just sits there for 3 minutes, then fails and retries.
No error message in the output file. Finally (after specified count of
retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I
have to examine the queue (table MSreplication_queue) and delete the
first respectively first few rows. Before deleting I execute the
corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
Query Analyzer, what succeeds. After deleting the rows from
MSreplication_queue I can restart Queue Reader Agent and now all works
properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237945.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827170
How many subscribers do you have? Queued Replication works best with under
10 subscribers and when most of the transactions originate on your
publisher.
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
"Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> I am using transactional replication with queued updating between two
> servers running SQL Server 2000 SP3. Publisher and distributor run
> both on the same server (a cluster machine). There is one subscriber.
> The Queue Reader Agent starts successfully, and transactions
> originating on either side are properly replicated. However, the Queue
> Reader occasionally fails. I enabled logging, but the output file
> looks normal to me; several queries for queued data, but then it seems
> to timeout. It just sits there for 3 minutes, then fails and retries.
> No error message in the output file. Finally (after specified count of
> retries) the agent shuts down with the message "Remote procedure call
> failed". When I try to restart Queue Reader Agent, same behavior. I
> have to examine the queue (table MSreplication_queue) and delete the
> first respectively first few rows. Before deleting I execute the
> corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
> Query Analyzer, what succeeds. After deleting the rows from
> MSreplication_queue I can restart Queue Reader Agent and now all works
> properly up to the next fail. :-(
> Does anyone have any advice? Thanks in advance
> Dorrit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237945.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827170
|||"" wrote:
> How many subscribers do you have? Queued Replication works
> best with under
> 10 subscribers and when most of the transactions originate on
> your
> publisher.
> --
> 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
> "Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> between two
> distributor run
> subscriber.
> the Queue
> file
> then it seems
> retries.
> specified count of
> procedure call
> behavior. I
> delete the
> the
> sp_MSsync_upd_MyTable) in
> all works
> standards
> http://www.dbforumz.com/Replication-...ict237945.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827170
There is one subscriber.

Queue Reader failed for Transactional Repl

Hello,
I have set up queued updating Transactional Repl. The Queue Reader keeps
failing and cannot start up any more. The output shows the following error
"cannot have more than one instance of queue reader agent for the
distribution database". What does this error message mean? Please help.
Thanks in advances
Please can you post up the complete error message, including the error
number?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your reply. Finally I solved by shutting down the queue reader
exe on the task manager. I thought the exe would not run if the Queue Reader
fails. But it is cluster environment and it might be a bug somewhere with the
Queue Reader exe. After I shut down the process, restarting Queue Reader
agent worked fine.
have a good one,
FJY
"Paul Ibison" wrote:

> Please can you post up the complete error message, including the error
> number?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Queue Reader agent fails to start when stopped through Enterprise Menager

I've set up transactional replication with queued updating. If I stop the queue reader agent through EM, I can't start it again. Agent's history says "Queue Reader aborting. The step failed."
All servers are SQL Server 2000 SP3.
Help is greatly appreciated!OK, I found what my problem was (actually not really mine - it's all EM's fault). When you stop the Queue Reader agent through EM, in reality that process is not stoped, but continues to run on the machine. However, the EM doesn't see it and when you try to start it again, of course , it doesn't want to start another instance of Queue Reader agent.

Basically, I had to go and manually stop the process, and only then start it again through EM.

Bad, bad Enterprise Manager!

Queue reader agent aborting

Hi,
I'm running transactional replication (SQL Server 2000) with queued updating
between two locations (New Jersey & Arizona). Generally it runs fine, but
about every other week the Queue Reader agent fails with an error message
"Queue reader aborting. The step failed". I can't find any information
regarding the source or nature of the error, and if I try to restart the
agent if fails immediately with the same error. The only way I've been able
to get around the error is to drop the subscription and redo the snapshot.
Does anyone have any suggestions how to determine the source of this error?
Thanks for any help...
Ed
Can you do logging for your queue reader and post the error message you get back here?
Follow the instructions in this kb for more information on how to enable it.
http://support.microsoft.com/default...b;EN-US;312292
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:

> Hi,
> I'm running transactional replication (SQL Server 2000) with queued updating
> between two locations (New Jersey & Arizona). Generally it runs fine, but
> about every other week the Queue Reader agent fails with an error message
> "Queue reader aborting. The step failed". I can't find any information
> regarding the source or nature of the error, and if I try to restart the
> agent if fails immediately with the same error. The only way I've been able
> to get around the error is to drop the subscription and redo the snapshot.
> Does anyone have any suggestions how to determine the source of this error?
> Thanks for any help...
> Ed
>
>
|||Thanks, I'll try adding that logging parameter. What's the best way to stop
the queue reader agent? In the past, I stopped it from enterprise manager by
right clicking and selecting "Stop Agent", but once I do that it often won't
start again...
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> Can you do logging for your queue reader and post the error message you
get back here?
> Follow the instructions in this kb for more information on how to enable
it.[vbcol=seagreen]
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
updating[vbcol=seagreen]
but[vbcol=seagreen]
message[vbcol=seagreen]
able[vbcol=seagreen]
snapshot.[vbcol=seagreen]
error?[vbcol=seagreen]
|||For me the only reliable way to stop the queue reader and log reader agents is to stop the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:

> Thanks, I'll try adding that logging parameter. What's the best way to stop
> the queue reader agent? In the past, I stopped it from enterprise manager by
> right clicking and selecting "Stop Agent", but once I do that it often won't
> start again...
> Ed
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> get back here?
> it.
> updating
> but
> message
> able
> snapshot.
> error?
>
>
|||Thanks again. Now I'm just waiting for it to fail...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:40F318EA-ACF6-4B3A-865D-7B33B00F305E@.microsoft.com...
> For me the only reliable way to stop the queue reader and log reader
agents is to stop the SQL Server agent.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
stop[vbcol=seagreen]
manager by[vbcol=seagreen]
won't[vbcol=seagreen]
you[vbcol=seagreen]
enable[vbcol=seagreen]
fine,[vbcol=seagreen]
information[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
|||Hi,
The queue reader failed. Below is the output from the log. Any insight you
have would be greatly appreciated.
Ed
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [SLIM].12
Connecting to QueueReader 'SLIM.Billing'
Server: SLIM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
[6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
MSqreader_agents
Server: SLIM, Database Billing : ODBC Error:Invalid object name
'MSqreader_agents'.
Unable to connect to Local Distributor
Queue Reader aborting
|||can you check to see if this table exists in your local distribution database?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:

> Hi,
> The queue reader failed. Below is the output from the log. Any insight you
> have would be greatly appreciated.
> Ed
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [SLIM].12
> Connecting to QueueReader 'SLIM.Billing'
> Server: SLIM
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
> [6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
> MSqreader_agents
> Server: SLIM, Database Billing : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> Unable to connect to Local Distributor
> Queue Reader aborting
>
>
|||The table does exist - but I thought it was odd that the statements
contained references to the "Billing" database. That's a database on the
server, but it's not part of replication. Digging into the job under SQL
Server Agent, I saw that the queue reader step was set to run in the
"Billing" database. I'm guessing someone changed that setting
unintentionally. I changed it to run under the "distribution" database, and
the step restarted fine.
Thanks for the tip on logging the output - it would have been impossible to
figure this out without it
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:810FBB09-2770-4D9C-A07B-ED917F4B9E32@.microsoft.com...
> can you check to see if this table exists in your local distribution
database?[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
you[vbcol=seagreen]
(Billing)'[vbcol=seagreen]

Queue Reader Aborting

I am using transactional replication with queued updating. The publisher is
located in Norway, with a single subscriber in the US. The Queue Reader
Agent starts successfully, and transactions originating on either side are
properly replicated. However, the Queue Reader agent eventually shuts down
with the message "Queue Reader Aborting". When I check the session log, I
find occurrences of the error: "Server does not exist or access denied"
throughout the entire time the agent runs. Right before the agent actually
shuts down, I get the following three errors:
"Communication link failure"
"Error in caching messages from SQL Queue"
"Queue Reader Aborting"
After the queue reader fails, it does not restart automatically - it
requires manual intervention. We are aware that the connection is sporatic,
and can accept the queue building up on both sides. Is the communication
link failure a result of some threshold being reached? Can that threshold be
removed? If not, is there a way to have the queue reader agent automatically
restart?
Hi,
I have a replication scenario similar to yours, and my queue reader also
periodically aborts with those same messages. I don't have any insight yet
as to what causes it, but at least now you know you're not alone!
Ed
"Daniel Inman" <DanielInman@.discussions.microsoft.com> wrote in message
news:914EA9FB-1554-45F7-BC84-47E44D56161C@.microsoft.com...
> I am using transactional replication with queued updating. The publisher
is
> located in Norway, with a single subscriber in the US. The Queue Reader
> Agent starts successfully, and transactions originating on either side are
> properly replicated. However, the Queue Reader agent eventually shuts
down
> with the message "Queue Reader Aborting". When I check the session log, I
> find occurrences of the error: "Server does not exist or access denied"
> throughout the entire time the agent runs. Right before the agent
actually
> shuts down, I get the following three errors:
> "Communication link failure"
> "Error in caching messages from SQL Queue"
> "Queue Reader Aborting"
> After the queue reader fails, it does not restart automatically - it
> requires manual intervention. We are aware that the connection is
sporatic,
> and can accept the queue building up on both sides. Is the communication
> link failure a result of some threshold being reached? Can that threshold
be
> removed? If not, is there a way to have the queue reader agent
automatically
> restart?