Really Urgent

  • Hi

    We have three servers so I wanted to enable replication on these servers so background for this installtion is Server A is Publisher, ServerB is Distributor and Server C is Subscriber. I had started setting up replication in our environment with remote distributor on Server B and I was struck here and the thing that I did here is created distributor on Server B and dropeed it and I want to clean up and start settin up from scratch.

    Here are the steps I did it after the distributor database has been created on server B.

    use master

    go

    alter database distribution set offline;

    drop database distribution;

    USE MASTER

    GO

    create database distribution on

    (filename=''),

    (filename='')

    FOR Attach

    go

    ----------------------------------------------------------------------------------------------------

    Then I tried disabling publishing and distribution using SSMS this is the information I get

    -------------------------------------------------------------------------------------------------------

    TITLE: Microsoft.SqlServer.ConnectionInfo

    ------------------------------

    SQL Server could not disable publishing and distribution on 'XXXXXXX'.

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    sp_MSdistpublisher_cleanup can only be executed in the "distribution" database.

    Changed database context to 'master'. (Microsoft SQL Server, Error: 21482)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=21482&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Thanks,

    Ravi

  • The error message is because you dropped the distribution database before you were done disabling replication. Any particular reason you did that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I didnot know how to move forward with enabling replication so, I dropped it. can you please let me know how to move forward.

    Thanks,

    Ravi

  • You're in kind of uncharted waters on this one.

    I'd try just setting up Replication again, as if it were never there. You'll end up with some orphan artifacts on some of the servers, but that shouldn't have any functional impact. Can't guarantee that'll work, but it's what I'd try next.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • HI

    The one thing I did is created a distribution on Server B and dropped the database. I did not even enabled the replication or created any publisher/subscribers.

    and when I tried this step

    USE MASTER

    GO

    create database distribution on

    (filename=''),

    (filename='')

    FOR Attach

    go

    The distribution database was up but this db was not created under system databases instead it was created in user databases.

    Thanks,

    Ravi

  • Try this: http://msdn.microsoft.com/en-us/library/ms151192.aspx The idea here is that you cannot just create a distribution database the way that you did. This page will show you how to do it from SSMS, but there are also links on the left on how to script it.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • Also, for what reasons are you using a second server as the distributor? There are reasons for this, but there may be better reasons to keep the publisher the distributor and use the extra server for something else like a mirrored backup or something...

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • Hi Jared.

    The reason here is we wanted all the publisher, distributor, subscriber to be independent to each other the database is used constantly by application and we did not wanted to have any performance issues that is the reason why we did that and part of it I started Server B and configured distributor and later I dropped it and again I created distributor with the above steps that I mentioned in my previous post and Im unable to remove the database distribution and now I want to create the replication from the scratch and some orphaned infomation sitting on server B and I want to create distribution on server B and it is not allowing me to do it. can you please let me know how can I clean up this entire mess that I had done.

    Thanks,

    Ravi

  • Ok, this will not work:

    USE MASTER

    GO

    create database distribution on

    (filename=''),

    (filename='')

    FOR Attach

    go

    This creates a user database named [distribution].

    I don't understand what the current issue is... You want to remove distribution in the system databases? Or you want to create it?

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • If you got here as I did because of this error:

    sp_MSdistpublisher_cleanup can only be executed in the database.

    Changed database context to 'master'. (Microsoft SQL Server, Error: 21482)

    And the issue is you can't setup or alter Publishing/Distribution on your server because someone deleted your distribution database and all you want to do is remove that reference to the Distribution database (mine was not actually called 'distribution'), then go to this post[/url]:

    The part that worked for me (I wanted publishing/distribution/replication gone and had *nothing* to lose, use at your own risk...refer back to the post above before doing anything):

    [font="Courier New"]exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

    GO[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply