none
Subscription Already Exists

    Frage

  • I am running SQL Server 2014 on Windows Server 2012 R2 Standard. The database server is setup to be both a publisher and distributor for replication out to several remote databases running on Windows 7. One of the remote computers running windows 7 had to be replaced. Once the new computer was setup and running, I deleted the existing subscription using SSMS. The new computer had to be named the same as the old computer that was taken out of commission. I tried to setup replication on the new computer and I get the following error.

    Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.
    Parameter 2 is incorrect for this DBCC statement.
    Supply either @credential_id or @credential_name.
    Changed database context to 'ScaleFusion'. (Microsoft SQL Server, Error: 14058)

    I have done this many times before without any issues. Now I have 4 machines behaving in the same way. I have tried to find information on how to address this issue but so far I have found nothing that has been of any help. Please let me know what I can do to fix this.



    • Bearbeitet JohnSQL789 Sonntag, 3. April 2016 22:40
    Sonntag, 3. April 2016 21:52

Antworten

Alle Antworten

  • Do you see entry for you subscription is the publication DB when you do

    select * from [dbo].[syssubscriptions]

    If thats the case, execute the below.

    use Publishing_DB 
    exec sp_dropsubscription @publication =N'Publication_Name', @subscriber = N'Subscriber_InstanceName', @article = N'all', @destination_db = N'Subscriber_DB'
    GO

    Montag, 4. April 2016 00:28
  • More tables to check:

    USE distribution
    SELECT * FROM dbo.MSdistribution_agents
    SELECT * FROM dbo.MSsubscriptions

    USE <pub_db>
    SELECT * FROM dbo.syssubscriptions


    IF this IS a Pull sub THEN CHECK these tables AS well:

    USE <sub_db>
    SELECT * FROM MSreplication_subscriptions
    SELECT * FROM dbo.MSsubscription_agents
    SELECT * FROM dbo.MSsubscription_prope


    Yaniv Etrogi
    site | blog | linked in | mail
    Please click the Mark as Answer button if a post solves your problem! or Vote As Helpful

    Montag, 4. April 2016 10:14
  • I do not have a syssubscriptions table but I do have dbo.sysmergesubscriptions. I see that the subscriber_server column lists the subscriber name that I am trying to use. I am not sure how to drop that info just yet. I don't want to mess up anything else.
    Montag, 4. April 2016 15:33
  • You can use sp_dropmergesubscription to clean it up.

     https://msdn.microsoft.com/en-us/library/ms176045.aspx

    The problem you have is because there are still information leftover in the system tables for the subscription you have removed. Which is why you are running into this error.

    The other option I can think of is creating a new publication just for this subscriber. Not sure if that is feasible for you or not as you will have one more publication to maintain. 


    Jithin Jerald

    Montag, 4. April 2016 16:00
  • This is what I have discovered. The dbo.MSmerge_subscriptions table (on the distribution database) does not contain the subscriber name that I want to use. But if I go to the publisher database I see an entry in the subscriber_server column on the dbo.sysmergesubscriptions table that matches what I want to call the new subscription. So essentially the two table don't match.

    use distribution
    select * from dbo.MSmerge_subscriptions
    order by subscriber
     
     use <PUB_DB>
     select * from dbo.sysmergesubscriptions
     order by subscriber_server

    Montag, 4. April 2016 16:12
  • so if the two tables don't match is there a safe way to make them match? Can I delete the row from the dbo.sysmergesubscriptions table?

    Mittwoch, 6. April 2016 21:06
  • It looks like you have a defunct subscription on the publisher. Drop it using  sp_dropmergesubscription 


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Donnerstag, 7. April 2016 11:14
    Moderator
  • Thanks everyone for your suggestions. What I ended up doing was creating a new publication that is specific to SQL2014. As we replace the old machines with SQL2005 I will put the subscriptions in the publication.

    John

    Donnerstag, 21. April 2016 19:29
  • I tried all of the above but could not clean up replication enough. 

    dropping subscription, and dropping publication, clean up all system tables, then redo publication & subscription but still get the error. 

    At the end I found a thread suggesting to turn replication off then on for the publisher db and that works, I was able to recreate the publication and subscription sucessfully. 

    exec sp_replicationdboption @dbname = N'XXXX', @optname = N'publish', @value = N'false'
    GO

    exec sp_replicationdboption @dbname = N'XXXX', @optname = N'publish', @value = N'true'
    GO


    Danielle Nguyen

    Samstag, 20. Januar 2018 08:08
  • This worked for me :)

    sp_replflush
    go

    exec sp_replicationdboption @dbname = N'XXXX', @optname = N'publish', @value = N'false'
    GO

    exec sp_replicationdboption @dbname = N'XXXX', @optname = N'publish', @value = N'true'
    GO

    Montag, 9. Juli 2018 18:56