locked
Transaction replication create publication Invalid object name dbo.syspublications RRS feed

  • Question

  • I recently clean the replication( remove subscribers, publishers and distributer) .

    I create Successfully Distribution .Now when I try to create publisher I am getting following error. Any one can help me on this

    ==================================

    New Publication Wizard encountered one or more errors while retrieving publication names. (New Publication Wizard)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.0.2000.8&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantGetPubNames&LinkId=20476

    ===================================

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

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
       at Microsoft.SqlServer.Replication.TransPublication.InternalRefresh(Boolean load)
       at Microsoft.SqlServer.Replication.ReplicationObject.get_IsExistingObject()
       at Microsoft.SqlServer.Management.UI.PubMisc.IsExistPubName(String dbname, String pubname, ReplicationSqlConnection sqlConn)
       at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.PubNameExists(String pubname)
       at Microsoft.SqlServer.Management.UI.PubWizardFinishPage.OnLeavePage(LeavePageEventArgs e)

    ===================================

    Invalid object name 'dbo.syspublications'. (.Net SqlClient Data Provider)


    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)




    • Edited by ashwan Saturday, November 10, 2018 7:23 PM
    Saturday, November 10, 2018 7:16 PM

Answers

  • The first thing to do is to unpublish the databases.

    Locate the published database and unpublish them by using

    sp_replicationdboption 'databasename','publish','false'

    or 

    sp_replicationdboption 'databasename','merge publish','false'

    It appears like your meta data is inconsistent. You may need to create the syspublications table to be able to disable the published database.

    Here is the schema of this table.

    CREATE TABLE [dbo].[syspublications](
        [description] [nvarchar](255) NULL,
        [name] [sysname] NOT NULL,
        [pubid] [int] IDENTITY(1,1) NOT NULL,
        [repl_freq] [tinyint] NOT NULL,
        [status] [tinyint] NOT NULL,
        [sync_method] [tinyint] NOT NULL,
        [snapshot_jobid] [binary](16) NULL,
        [independent_agent] [bit] NOT NULL,
        [immediate_sync] [bit] NOT NULL,
        [enabled_for_internet] [bit] NOT NULL,
        [allow_push] [bit] NOT NULL,
        [allow_pull] [bit] NOT NULL,
        [allow_anonymous] [bit] NOT NULL,
        [immediate_sync_ready] [bit] NOT NULL,
        [allow_sync_tran] [bit] NOT NULL,
        [autogen_sync_procs] [bit] NOT NULL,
        [retention] [int] NULL,
        [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
        [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
        [alt_snapshot_folder] [nvarchar](255) NULL,
        [pre_snapshot_script] [nvarchar](255) NULL,
        [post_snapshot_script] [nvarchar](255) NULL,
        [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
        [ftp_address] [sysname] NULL,
        [ftp_port] [int] NOT NULL DEFAULT ((21)),
        [ftp_subdirectory] [nvarchar](255) NULL,
        [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
        [ftp_password] [nvarchar](524) NULL,
        [allow_dts] [bit] NOT NULL DEFAULT ((0)),
        [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
        [centralized_conflicts] [bit] NULL,
        [conflict_retention] [int] NULL,
        [conflict_policy] [int] NULL,
        [queue_type] [int] NULL,
        [ad_guidname] [sysname] NULL,
        [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
        [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
        [min_autonosync_lsn] [binary](10) NULL,
        [replicate_ddl] [int] NULL DEFAULT ((1)),
        [options] [int] NOT NULL DEFAULT ((0))
    ) ON [PRIMARY]

    refer


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, November 11, 2018 3:05 AM
  • Hi ashwan,

    Do you mean that you are migrating a replicated database? Did you restore on the same instance or other? On the previous database, there is already existing a 'dbo.syspublications'.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 13, 2018 8:50 AM

All replies

  • The first thing to do is to unpublish the databases.

    Locate the published database and unpublish them by using

    sp_replicationdboption 'databasename','publish','false'

    or 

    sp_replicationdboption 'databasename','merge publish','false'

    It appears like your meta data is inconsistent. You may need to create the syspublications table to be able to disable the published database.

    Here is the schema of this table.

    CREATE TABLE [dbo].[syspublications](
        [description] [nvarchar](255) NULL,
        [name] [sysname] NOT NULL,
        [pubid] [int] IDENTITY(1,1) NOT NULL,
        [repl_freq] [tinyint] NOT NULL,
        [status] [tinyint] NOT NULL,
        [sync_method] [tinyint] NOT NULL,
        [snapshot_jobid] [binary](16) NULL,
        [independent_agent] [bit] NOT NULL,
        [immediate_sync] [bit] NOT NULL,
        [enabled_for_internet] [bit] NOT NULL,
        [allow_push] [bit] NOT NULL,
        [allow_pull] [bit] NOT NULL,
        [allow_anonymous] [bit] NOT NULL,
        [immediate_sync_ready] [bit] NOT NULL,
        [allow_sync_tran] [bit] NOT NULL,
        [autogen_sync_procs] [bit] NOT NULL,
        [retention] [int] NULL,
        [allow_queued_tran] [bit] NOT NULL DEFAULT ((0)),
        [snapshot_in_defaultfolder] [bit] NOT NULL DEFAULT ((1)),
        [alt_snapshot_folder] [nvarchar](255) NULL,
        [pre_snapshot_script] [nvarchar](255) NULL,
        [post_snapshot_script] [nvarchar](255) NULL,
        [compress_snapshot] [bit] NOT NULL DEFAULT ((0)),
        [ftp_address] [sysname] NULL,
        [ftp_port] [int] NOT NULL DEFAULT ((21)),
        [ftp_subdirectory] [nvarchar](255) NULL,
        [ftp_login] [sysname] NULL DEFAULT (N'anonymous'),
        [ftp_password] [nvarchar](524) NULL,
        [allow_dts] [bit] NOT NULL DEFAULT ((0)),
        [allow_subscription_copy] [bit] NOT NULL DEFAULT ((0)),
        [centralized_conflicts] [bit] NULL,
        [conflict_retention] [int] NULL,
        [conflict_policy] [int] NULL,
        [queue_type] [int] NULL,
        [ad_guidname] [sysname] NULL,
        [backward_comp_level] [int] NOT NULL DEFAULT ((10)),
        [allow_initialize_from_backup] [bit] NOT NULL DEFAULT ((0)),
        [min_autonosync_lsn] [binary](10) NULL,
        [replicate_ddl] [int] NULL DEFAULT ((1)),
        [options] [int] NOT NULL DEFAULT ((0))
    ) ON [PRIMARY]

    refer


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, November 11, 2018 3:05 AM
  • Hi Av



    • Edited by ashwan Sunday, November 11, 2018 1:06 PM
    Sunday, November 11, 2018 1:05 PM
  • Hi ashwan,

    Do you mean that you are migrating a replicated database? Did you restore on the same instance or other? On the previous database, there is already existing a 'dbo.syspublications'.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 13, 2018 8:50 AM
  • hi Teige

    No migration.  REplication 2008R3 to 2014 RTM version. 

    Wednesday, November 14, 2018 9:26 AM
  • Hi Av



    Where did you restore the database to?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 15, 2018 8:48 AM