Re: sp_MSsetupbelongs Error

From: Debra Dove [MS] (ddove_at_online.microsoft.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 23:19:42 GMT

This issue has been documented in a SQL Server 2000 KB article. Please
checkout:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811028&Product=sql2k
 

In addition to the suggestions in the KB article, please review the
whitepaper:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/sql_replmergepartitioned.asp

When using filters and join filters, it is important that particular
columns are indexed properly to ensure the fastest queries possible. Using
this same mechinism described below, you can change the QueryTimeout value
for all merge agents for your SQLCE subscribers. The default is 5 minutes
(300 seconds), but your queries, potentially due to the lack of indexes, is
running longer than 5 minutes.

Change the QueryTimeout property controlled via the Merge Agent Profile.
To change the property value: In Enterprise Manager, highlight on your
server, select 'Tools', 'Replication', 'Configure Publishing, Subscribers,
and Distribution ...' From the Distributor Tab, select the 'Agent Profiles'
button. Select the 'Merge Agent' tab. Click the 'New Profile' button, name
the Profile and change the 'QueryTimeout' to a larger value than 300. Then
set this new Profile as your Default by clicking on the radial button in
the default column.

Some level of performance improvement of overall sync can also achieved by
reducing the Merge Agent logging occurring on the SQL Server. This
property is controlled via the Merge Agent Profile. To change the property
value: In Enterprise Manager, highlight on your server, select 'Tools',
'Replication', 'Configure Publishing, Subscribers, and Distribution ...'
>From the Distributor Tab, select the 'Agent Profiles' button. Select the
'Merge Agent' tab. Click the 'New Profile' button, name the Profile and
change the 'HistoryVerboseLevel' from 1 to 0. Then set this new Profile as
your Default by clicking on the radial button in the default column.

--------------------
>>NNTP-Posting-Date: Tue, 30 Mar 2004 13:15:43 -0600
>>From: Matt <none@none.com>
>>Newsgroups: microsoft.public.sqlserver.ce
>>Subject: Re: sp_MSsetupbelongs Error
>>Date: Tue, 30 Mar 2004 20:15:08 +0100
>>Organization: -
>>Reply-To: -
>>Message-ID: <vohj60lpbhn6rjipo6s5r2k8pp146n6eek@4ax.com>
>>References: <echj60l3ush7v9iojfs91u7tfsj8nobk7q@4ax.com>
>>X-Newsreader: Forte Agent 1.91/32.564
>>MIME-Version: 1.0
>>Content-Type: text/plain; charset=us-ascii
>>Content-Transfer-Encoding: 7bit
>>Lines: 39
>>X-Trace:
sv3-wmkKyn0v/rP6UGjYSNqWhTlOVqct9uzTGR7hZGVvxmKw8X3r1IFYHT4MWuJhKv5lW2mfbveQ
vgrnIos!odEck3M4TibyJypXG2HOdTbxSy166Ur0Gqz3JvAcPURotgd1Hh18SAG0mEYxVl8ZLQP0
kg==
>>X-Complaints-To: abuse@giganews.com
>>X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
>>X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
>>X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your
complaint properly
>>X-Postfilter: 1.1
>>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.icl.net!newsfeed.news2me.com!newsfeed3.easynews.com!easynews.c
om!easynews!border1.nntp.sjc.giganews.com!nntp.giganews.com!local1.nntp.sjc.
giganews.com!news.giganews.com.POSTED!not-for-mail
>>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:11789
>>X-Tomcat-NG: microsoft.public.sqlserver.ce
>>
>>Sorry, that should say: SQL Server 2000 SP3a
>>
>>On Tue, 30 Mar 2004 20:09:43 +0100, Matt <none@none.com> wrote:
>>
>>>Hi there
>>>
>>>Client/Subscriber: HP iPAQ 5500, CF 1.0 SP2 (re-release) VB.NET app
>>>Server/Publisher: SQL Server 2000 SP 4, SQL CE 2.0
>>>
>>>We have an application going through user testing and we're getting a
>>>lot of synchronization problems in which we receive the following
>>>error:
>>>
>>>Error # 1 of 4; Error Code: -2147467259; Message: ; Minor Err: 29006;
>>>Source: Microsoft SQL Server 2000 Windows CE Edition;
>>>Error # 2 of 4; Error Code: -2147200925; Message: Failed to enumerate
>>>changes in the filtered articles.; Minor Err: 0; Source: Merge
>>>Replication Provider;
>>>Error # 3 of 4; Error Code: 0; Message: {call
>>>sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?)}; Minor Err: 0; Source:
>>>MSAPP14;
>>>Error # 4 of 4; Error Code: 0; Message: The merge process timed out
>>>while executing a query. Reconfigure the QueryTimeout parameter and
>>>retry the operation.; Minor Err: 0; Source: MSAPP14
>>>
>>>This problem manifests itself as a 5-minute sync on the PDA, which
>>>eventually fails. We don't know what is causing this error to start,
>>>but once it occurs, the synchronization fails every time. The only
>>>way we can resolve the problem is to delete the local .sdf from the
>>>PDA.
>>>
>>>This is causing us huge problems and is potentially threatening the
>>>success of the project. Does anyone know why this is occurring? I
>>>can supply more information if required.
>>>
>>>Thanks in advance.
>>>
>>>Matt
>>
>>

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