none
Cannot Configure Distribution RRS feed

  • Question

  • Forum,

    I cannot create a Distribution Server on SQL Server 2005 SP2. When I right click on Replication and click "Configure Distribution" and hour glass appears for a minute, and then nothing happens.

    I get the same result if I try to create a publication.

    Any suggestions on what to do?

    Thank you!
    Wednesday, January 30, 2008 5:32 PM

Answers

  • Ray, it looks like to me that your distributor is now correctly configured. Can you try to create a publication now?

     

    Thursday, January 31, 2008 1:15 PM
    Moderator

All replies

  • This is difficult to diagnose.


    Try the following to see where it is hanging:


    sp_adddistributor @@servername
    GO
    sp_adddistributiondb 'distribution'
    GO
    sp_adddistpublisher @@servername, 'distribution',
    GO


    Post back any errors you get here!

     

    Wednesday, January 30, 2008 6:33 PM
    Moderator
  • Hilary, thank you for responding.

    Here's the error I get back:

    Msg 208, Level 16, State 1, Procedure sp_MSenum_replication_status, Line 39
    Invalid object name 'dbo.MSmerge_sessions'.
    Msg 15109, Level 16, State 1, Line 1
    Cannot change the owner of the master, model, tempdb or distribution database.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ','.
    Wednesday, January 30, 2008 6:37 PM
  • Can you issue these one by one and tell me which one it bombs on?

     

    Also my mistake on the final step it should be

     

    sp_adddistpublisher @@servername, 'distribution'
    GO

     

    Wednesday, January 30, 2008 9:13 PM
    Moderator
  • Hilary,

    Sorry, I didn't notice the errand comma.  Here's what I get when I run them individually:

    sp_adddistributor @@servername
    GO

    Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109
    The server 'MYSQLSRVR' is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard.

    sp_adddistributiondb 'distribution'
    GO

    Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
     
    Creating distribution tables
     
    Creating table MSmerge_subscriptions
    Creating clustered index ucMSmerge_subscriptions
    Creating clustered index usMSrepl_originators
    Creating table MSmerge_agents
    Creating clustered index ucMSmerge_agents
     
    Dropping all distribution stored procedures and functions that are now in resource or are obsolete
     
     
    Dropping all distribution stored procedures and functions that are created locally
     
    Creating 'fn_MSmask_agent_type'.
    Creating 'sp_MSset_syncstate'.
    Creating 'sp_MSadd_repl_commands27'.
    Creating 'sp_MSadd_replcmds'.
    Creating 'sp_MSremove_published_jobs'.
    Creating 'sp_MSsubscription_cleanup'.
    Creating 'sp_MSdelete_dodelete'.
    Creating 'sp_MSdelete_publisherdb_trans'.
    Creating 'sp_MSmaximum_cleanup_seqno'.
    Creating 'sp_MSdistribution_delete'.
    Creating 'sp_MSdistribution_cleanup'.
    Creating 'sp_MShistory_cleanup'.
    Creating 'sp_MSget_repl_version'.
    Creating view MSdistribution_status
    Creating 'sp_MSlog_agent_cancel'.
     
    Adding user 'guest'.
     
     
    Adding role 'replmonitor'.
     
    Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

    sp_adddistpublisher @@servername, 'distribution'
    GO

    Command(s) completed successfully.

    Thanks.

    Wednesday, January 30, 2008 9:52 PM
  • Ray, it looks like to me that your distributor is now correctly configured. Can you try to create a publication now?

     

    Thursday, January 31, 2008 1:15 PM
    Moderator
  • Hilary,

    Thank you for your help.  Everything is working now.  Do you have any idea what was wrong?
    Thursday, January 31, 2008 7:13 PM
  • I suspect there was some blocking going on which prevented the distribution database from being created. That and the extra commaSmile

     

    Thursday, January 31, 2008 8:52 PM
    Moderator
  • This post has solved a major headache for me.  I had exactly that issue with the hourglass and nothing, and also nothing in a SQL log, event log or any error messages.  Now I can proceed with a task from 6 months ago !!

     

    Thanks all

    Simon

     

    Friday, October 10, 2008 11:37 PM
  • Our OS hardware had problem, we reinstalled SQL, then drop the
    distributor database, ... had problem, got error: Invalid object name
    'msdb.dbo.MSdistributiondbs'. (Microsoft SQL Server, Error: 208)
    here is what I did, worked
    1. restore the old 'msdb' to database as old_msdb'
    2. script table [dbo].[MSdistributiondbs] from old_msdb' to run under the current msdb:
    use msdb
    CREATE TABLE [dbo].[MSdistributiondbs](
    [name] [sysname]
    NOT NULL,
    [min_distretention] [int] NOT NULL,
    [max_distretention] [int]
    NOT NULL,
    [history_retention] [int] NOT NULL
    ) ON
    [PRIMARY]

    GO
    3. when I try to use configure distributor, got another
    error: Invalid object name 'msdb.dbo.MSdistpublishers', then I did script table
    MSdistpublishers to current msdb
    use msdb
    CREATE TABLE
    [dbo].[MSdistpublishers](
    [name] [sysname] NOT NULL,
    [distribution_db]
    [sysname] NOT NULL,
    [working_directory] [nvarchar](255) NOT
    NULL,
    [security_mode] [int] NOT NULL,
    [login] [sysname] NOT
    NULL,
    [password] [nvarchar](524) NULL,
    [active] [bit] NOT
    NULL,
    [trusted] [bit] NOT NULL,
    [thirdparty_flag] [bit] NOT
    NULL,
    [publisher_type] [sysname] NOT NULL
    ) ON
    [PRIMARY]

    GO

    ALTER TABLE [dbo].[MSdistpublishers] ADD DEFAULT
    (N'MSSQLSERVER') FOR [publisher_type]
    GO
    Then, everything is fine. check 2 tables
    1. MSdistributiondbs
    2. MSdistpublishers
    are under msdb/table, not under msdb/table/systable. It is fine. Worked!!


    • Edited by east400 Friday, June 1, 2012 7:48 PM
    • Edited by Robin_Ren Friday, June 1, 2012 11:34 PM advertisement removed
    • Proposed as answer by Hany.Helmy Thursday, April 6, 2017 9:09 AM
    Friday, June 1, 2012 7:44 PM