Merge Replication Problems after upgrade to SQL Express

Hello,
I have the following scenario. An application using SQL Server Standard
Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
the client machine. Since the client is offline quite often merge replication
is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
upgrade (on the same box) worked fine and all clients could still
synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start
:-(
1) After the upgrade the entry within the Synchronization Manager is gone
(we can overcome this by using sp_MSregistersubscription or by manually
disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine. But if I try
to reinitialize the clients I get the following error:
Error messages:
The merge process could not clean up the subscription to
'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid
transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and
a few lookup tables) with 15MB. The same error occures if I drop the
subscription before the upgrade and recreate it afterwards. The only way to
get rid of this error is to drop the database and then recreate the
subscription.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Just some additional information:
The error seems to come from the exec sp_MSCleanupForPullReinit
N'Product',N'Product',N'tstvmw23' call. If I execute this sp manually
replication works again and the snapshot is reapplied also at the beginning
all tables are enumerated for changes.
All following reinit's will work fine afterwards.
I haven't tried to run sp_MSCleanupForPullReinit within a transaction but
that is the next step.
Greetings,
Thomas
"ThoHot00" wrote:

> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
> the client machine. Since the client is offline quite often merge replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Does this help?
http://support.microsoft.com/default.aspx/kb/916002
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a)
> on
> the client machine. Since the client is offline quite often merge
> replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and
> distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems
> start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I
> try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema
> and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way
> to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Hi Hilary,
thanks for your reply. I tried this already. I installed the hotfix on the
subscriber but that didn't help. I haven't applied it to the server since I
thought replication is pulled from the subscriber and the server shouldn't
cause this problem.
Thanks again - Greetings,
Thomas
"Hilary Cotter" wrote:

> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||Hi Hilary,
I applied the hotfix on the server and client now, restarted both machines
and the error is still there.
Greetings,
Thomas
"Hilary Cotter" wrote:

> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||In my experience the error actually referred to a timeout during the
already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
drop all the tables, stored procs, triggers etc within the timeout
period specified in WSM (default 30 seconds). It would be worth
profiling the client to see at what point the error occurs i.e. is it
exactly 30 seconds (or whatever your timeout is set to) after the
stored proc starts.
On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Hilary,
> I applied the hotfix on the server and client now, restarted both machines
> and the error is still there.
> Greetings,
> Thomas
> "Hilary Cotter" wrote:
>
>
>
>
>
>
>
|||Hi Tim,
thanks for the hint. This seems to be the problem (and also explain why this
error was so difficult to track). Do you now if it is possible to increase
the default timeout of WSM.
This also explains why begin trans; exec sp_MScleanupForPullReinit
...;rollback trans; solved the problem quite often. Because all data was
already in memory and therefore exec sp_MScleanupForPullReinit executed
within the 30s (the first time it requires 47s).
Greetings,
Thomas
"Tim" wrote:

> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
|||Hi Tim,
thanks again. I found out that I need to specify QueryTimeout as DWORD
within HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscription\<Server>:<Publi cation>:<Database>. If I
specify 120 there for QueryTimeout synchronization work again and Profiler
shows an execution time of 62241 for sp_MScleanupPullForReinit.
Microsoft should specify a better error message there - that would have
saved me a lot of time.
Greetings,
Thomas
"Tim" wrote:

> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
sql