QUEUE READER - A FREQUENT PROBLEM!

Hy, we have a recurrent problem on a transactional replication with queue
updating, after about 1
month the replication has worked well the queue reader stop with this error:
ODBC error invalid
sql data type, we have solved this problem in this way:
1 we have tracked the activity of the queue reader in a log file adding
-output parameter in the
run agent step of the queue reader job. In this way we have understood what
subscription cause
the crash of the queue reader agent
2 then we have deleted some transaction from the msreplication queue table
of this subscription;
in particular we note that the transaction that stop the agent has a
different data lenght in the field datalen of the table msreplqueue respect
the similar other ones.
After deleting the transaction replication the queue flush and replication
restart to work well... but after 15/30/60 days the problem happens for
another time and so on...
Does anybody know how I can definitively solve his problem ?
I have run into problems like this and they generally are bugs. Basically
what you need to do is to determine what dml on the subcriber is causing the
crash, then try to repro it, and present it to Microsoft.
I would contact PSS and ask them to help you to diagnose it. These kinds of
bugs can be very difficult to track down.
How many nodes are there in your queued environment?
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
"Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
news:4CB930CD-560C-4F49-BF81-23FEB9135DF9@.microsoft.com...
> Hy, we have a recurrent problem on a transactional replication with queue
> updating, after about 1
> month the replication has worked well the queue reader stop with this
> error:
> ODBC error invalid
> sql data type, we have solved this problem in this way:
> 1 we have tracked the activity of the queue reader in a log file adding
> -output parameter in the
> run agent step of the queue reader job. In this way we have understood
> what
> subscription cause
> the crash of the queue reader agent
> 2 then we have deleted some transaction from the msreplication queue table
> of this subscription;
> in particular we note that the transaction that stop the agent has a
> different data lenght in the field datalen of the table msreplqueue
> respect
> the similar other ones.
> After deleting the transaction replication the queue flush and replication
> restart to work well... but after 15/30/60 days the problem happens for
> another time and so on...
> Does anybody know how I can definitively solve his problem ?
|||thanks Hilary for your answer, I have a publisher and distributor on one
server, one pubblication and 5 subscrivers that subscrive this pubblication.
I escuse for my english but What do yuo mean "How many nodes are there in
your queued environment" ? I'm wondering is it possible to read transaction
in the queue? Is it possible that one transaction cold be inserted in the
queue table and the same transaction coldn't be inserted at the publisher?
Perhaps the answer of the last question is, as you already wrote, there is a
microsoft bug, don't you think so?.
Do you think that one possible solution is to upgrate replication with
service pack 4? or do you think the better choice is to install sql server
2005 and recreate replication?
Have you solve your problem contact PSS?
Tankyou Hilary
"Hilary Cotter" wrote:

> I have run into problems like this and they generally are bugs. Basically
> what you need to do is to determine what dml on the subcriber is causing the
> crash, then try to repro it, and present it to Microsoft.
> I would contact PSS and ask them to help you to diagnose it. These kinds of
> bugs can be very difficult to track down.
> How many nodes are there in your queued environment?
> --
> 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
>
> "Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
> news:4CB930CD-560C-4F49-BF81-23FEB9135DF9@.microsoft.com...
>
>
|||IMHO, Microsoft won't even talk to you unless you have the latest sp. I
would apply it everywhere and then see if your problem reoccurs. I had a
problem where I updated a text field to '' and it caused a very difficult to
track down bug. We then updated the text field to ' ' and it went away. This
took about 3 months to figure out.
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
"Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
news:E102BCD1-77D2-4579-A34F-C5547DA01462@.microsoft.com...[vbcol=seagreen]
> thanks Hilary for your answer, I have a publisher and distributor on one
> server, one pubblication and 5 subscrivers that subscrive this
> pubblication.
> I escuse for my english but What do yuo mean "How many nodes are there in
> your queued environment" ? I'm wondering is it possible to read
> transaction
> in the queue? Is it possible that one transaction cold be inserted in the
> queue table and the same transaction coldn't be inserted at the
> publisher?
> Perhaps the answer of the last question is, as you already wrote, there is
> a
> microsoft bug, don't you think so?.
> Do you think that one possible solution is to upgrate replication with
> service pack 4? or do you think the better choice is to install sql server
> 2005 and recreate replication?
> Have you solve your problem contact PSS?
> Tankyou Hilary
> "Hilary Cotter" wrote:
|||Ok Hilary, the first time the industrial production will stop I'll update sql
server with the latest sp or perhaps I'll install sql server 2005; In your
opinion what is the best choice: instal sp on sql server 2000 or install sql
server 2005?, We are experimenting sql server 2005 replication making crash
test for future implementations, my idea is that 2005 replication has
improved respect 2000 replication, in terms of services and performance,
don't you think so? I'm wondering if 2005 replications are more reliable
respect 2000 replications? I mean the agents still fail for many reasons like
in 2000 replication?
"Hilary Cotter" wrote:

> IMHO, Microsoft won't even talk to you unless you have the latest sp. I
> would apply it everywhere and then see if your problem reoccurs. I had a
> problem where I updated a text field to '' and it caused a very difficult to
> track down bug. We then updated the text field to ' ' and it went away. This
> took about 3 months to figure out.
> --
> 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
>
> "Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
> news:E102BCD1-77D2-4579-A34F-C5547DA01462@.microsoft.com...
>
>
|||If it is a bug or something in your data, it may crash on SQL 2005 as well,
so this upgrade may or may not solve the problem. I think your best approach
would be to apply the latest sp, run profiler and wait for the problem.
Preferably contact PSS and have them trouble shoot the issue with you. Their
first recommendation will be to apply the sp.
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
"Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
news:EEC49AFB-1EBF-4689-809B-704008FFB734@.microsoft.com...[vbcol=seagreen]
> Ok Hilary, the first time the industrial production will stop I'll update
> sql
> server with the latest sp or perhaps I'll install sql server 2005; In your
> opinion what is the best choice: instal sp on sql server 2000 or install
> sql
> server 2005?, We are experimenting sql server 2005 replication making
> crash
> test for future implementations, my idea is that 2005 replication has
> improved respect 2000 replication, in terms of services and performance,
> don't you think so? I'm wondering if 2005 replications are more reliable
> respect 2000 replications? I mean the agents still fail for many reasons
> like
> in 2000 replication?
> "Hilary Cotter" wrote:
|||Thanks Hilary, I'm going to install SP and trace with profiler the processes
wich execute DML on subscrivers and publisher. I note that i can trace only
the idname of the process or IDTransaction but not the specifical command (I
mean SQL DML command). Is there a way to find out the SQL Command having the
transactionID?
"Hilary Cotter" wrote:

> If it is a bug or something in your data, it may crash on SQL 2005 as well,
> so this upgrade may or may not solve the problem. I think your best approach
> would be to apply the latest sp, run profiler and wait for the problem.
> Preferably contact PSS and have them trouble shoot the issue with you. Their
> first recommendation will be to apply the sp.
> --
> 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
>
> "Luca Schiavon" <LucaSchiavon@.discussions.microsoft.com> wrote in message
> news:EEC49AFB-1EBF-4689-809B-704008FFB734@.microsoft.com...
>
>
|||Now i have just understood how i can also track with profiler SQL command DML
!!! Thankyou Hilary
"Luca Schiavon" wrote:
[vbcol=seagreen]
> Thanks Hilary, I'm going to install SP and trace with profiler the processes
> wich execute DML on subscrivers and publisher. I note that i can trace only
> the idname of the process or IDTransaction but not the specifical command (I
> mean SQL DML command). Is there a way to find out the SQL Command having the
> transactionID?
> "Hilary Cotter" wrote: