old sysmergesubscriptions records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: will (me_at_privacy.net)
Date: 03/23/04 

Date: Wed, 24 Mar 2004 10:37:39 +1100

Hi,

Could someone tell me a procedure to manually (or otherwise) clean
sysmergesubscriptions and related tables?

We have a replication system with hundreds of merge pull subscriptions
(which are fine), however, there are also hundreds of push subscriptions
that have appeared in the sysmergesubscriptions tables all over the place.
The push subscriptions are all subscribing from and to the same server name,
which is odd (is this someweird loopback?).

sp_mergesubscription_cleanup and sp_subscription_cleanup have no effect and
the push subscriptions are not in MSmerge_subscriptions and do not show up
in enterprise manager.

my email address is on http://fileant.com if you need it.

here is a sample (a bit messy sry)
it might help to paste it into notepad or ultraedit

here TEST-SERVER has 2 push subscription's to TEST-SERVER which would be
nice to clean away. (TESTNET-SERVER is the publisher / distributor)
It also has two local pull subscriptions to TESTNET-SERVER which i want to
keep.

select * from sysmergesubscriptions where subscriber_server = 'TEST-SERVER'
and db_name='TESTMTG'

subid partnerid
datasource_type datasource_path
srvid db_name
pubid status subscriber_type
subscription_type priority sync_type description
login_name
last_validated subscriber_server
use_interactive_resolver publication
distributor
validation_level resync_gen attempted_validate
last_sync_date last_sync_status
last_sync_summary
------------------------------------ ------------------------------------ --
------------- --------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------- ----------- ----------------------
----------------------------------------------------------------------------
------------------------------ ------------------------------------ ------ -
-------------- ----------------- ------------------------ --------- --------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------- --------------------------------------------------------
------------------------------------------------------------------------ ---
--------------------------------------------------- ------------------------
----------------------------------------------------------------------------
---------------------------- ------------------------ ----------------------
----------------------------------------------------------------------------
------------------------------ ---------------------------------------------
----------------------------------------------------------------------------
------- ---------------- ----------- ---------------------------------------
--------------- ------------------------------------------------------ -----
----------- ----------------------------------------------------------------
----------------------------------------------------------------
C94A2AC9-9F1C-4BCA-9CCA-6E2EB65FB794 D91A3159-680D-4654-993D-6284556F2887 0
NULL
0 TESTMTG
D91A3159-680D-4654-993D-6284556F2887 1 2 1
0.0 2 TESTNET-SERVER.TESTMTG.LoanProducts
sa2
NULL TEST-SERVER
0 LoanProducts
TESTNET-SERVER
0 -1 NULL
NULL NULL NULL
D840A201-A7F9-46B6-9D33-CFD8932D0403 D840A201-A7F9-46B6-9D33-CFD8932D0403 0
0 TESTMTG
D840A201-A7F9-46B6-9D33-CFD8932D0403 1 1 0
70.0 1 NULL
TEST-DOMAIN\Administrator
NULL TEST-SERVER
0 LoanProducts
TEST-SERVER
0 -1 NULL
NULL NULL NULL
F62B42D9-BE21-4D4F-9EFD-DAD42B239FF9 8E6215AF-8B44-4F7D-B982-114DB2CF3D6C 0
NULL
         0 TESTMTG
8E6215AF-8B44-4F7D-B982-114DB2CF3D6C 1 2 1
0.0 2 TESTNET-SERVER.TESTMTG.UserData
sa2
2004-03-18 18:52:28.883 TEST-SERVER
0 UserData
TESTNET-SERVER
0 -1 2004-03-18 18:52:28.883
NULL NULL NULL
5E1F246D-8D93-4FF0-90BB-EB839F246CCD 5E1F246D-8D93-4FF0-90BB-EB839F246CCD 0
0 TESTMTG
5E1F246D-8D93-4FF0-90BB-EB839F246CCD 1 1 0
70.0 1 NULL

TEST-DOMAIN\Administrator
NULL TEST-SERVER
0 UserData
TEST-SERVER
0 -1 NULL
NULL NULL NULL

Relevant Pages

  • Re: Merge Subscription Notifications
    ... I knew that WSM was an option, ... the push subscriptions is the reason I was using them. ... to pull when it senses a network connection. ...
    (microsoft.public.sqlserver.replication)
  • Push subscription failing
    ... SQL Server Enterprise Manager encountered errors creating push subscriptions ... JANUS: Error 208: Invalid object name 'sysextendedarticlesview'. ... THe view sysextendedarticlesview exist in the publisher database. ...
    (microsoft.public.sqlserver.replication)
  • Re: The merge process could not update the list of subscriptions.
    ... The problem occured when cleaning up dead subscriptions & remote servers - which we do periodically. ... Cannot insert duplicate key in object 'sysmergesubscriptions'. ... > or using Push subscriptions. ...
    (microsoft.public.sqlserver.replication)
  • Re: The merge process could not update the list of subscriptions.
    ... article applies to SQL 7 as a subscriber to SQL 2000 publication - ... don't say for publisher, subscriber or both, so I assume it's both). ... or using Push subscriptions. ... case that the duplicate key value being added was due to two subscriptions ...
    (microsoft.public.sqlserver.replication)
  • Re: Array Help Needed
    ... // pull subscriptions ... foreach ... I have an array of ORDER DETAILS. ...
    (alt.php)