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

Merge replication broke after SQL 2005 Evaluation upgrade to Enterprise

I'm try to upgrade SQL Server 2005 Evaluation with tuned up and worked merge replication.

- I'm insert disk with licensed SQL Server 2005 Enterprise, start installation - in process, indicate instance for installing, in list of options appears I see option "ugrade"
- All passes fine and go on:

After upgarde I'm get completely broken merge replication!

Moreover, such feeling that certain strange troubles with SQL Agent:
all points context menu in Replication Monotor - disabled and if I try to start replication manually through jobs - too failed - with some error "agent shutdown" !

I'm try to build "clear" situation: install in Virtual PC Windows 2003 Ent SP1, SQL 2005 Eval and setup my test replication and upgrade SQL Server to Enterprise version - and get same trouble too!!!

What the best way to uprade my evaluations sql servers with replication to licensed version SQL Ent?

I've moved this thread to the replication forum, where you're more likely to get an answer.

Paul

|||

Hi, Nikolai,

What error message did you observe when you say merge replication is broken? Remember after you upgrade to SQL Server 2005 RTM (either from SQL 2000 or SQL 2005 Eval), you need to re-run snapshot job for merge replication upgrade to complete, from then you can run normal merge agent job, otherwise, you will hit error like "You must rerun snapshot because current snapshot files are obsolete.". Let me know if you're seeing a different error.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.