Replication Drop Edilemediği Zaman

Suspect moduna düşmek veya başka nedenlerden dolayı replikasyonu kaldıramadan yeniden restore edilmiş veya attach edilmiş bir veritabanında replikasyonu kaldırıp yeniden kurmaya çalıştığımızda aşağıdaki hata mesajıyla karşılabiliriz.


TITLE: Microsoft.SqlServer.ConnectionInfo
———-
SQL Server could not disable publishing and distribution on ‘Veri Tabanı Adı’.
———-
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
———-
Invalid object name ‘dbo.syssubscriptions’.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Changed database context to ‘master’. (Microsoft SQL Server, Error: 208)

Bu durumda ilk önce sözkonusu veritabanını replikasyonda kurtarmak yani unpublish moduna getirmek gerekir. Bunun için aşağıdaki komutlar kullanılabilir.

EXEC sp_replicationdboption 'Veri Tabanı Adı','publish','false'
EXEC sp_droppublication @publication='all',@ignore_distributor=1
EXEC sp_dropdistributor @no_checks =1

Bu durumda syspublications, sysextendedarticlesview gibi nesnelerin olmadığına dair hata verebilir. Bunun en kısa çözümü sözkonusu eksik nesneleri publication konumundaki veri tabanı üzerinde oluşturmaktır.

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]
CREATE TABLE [dbo].[sysarticles](
	[artid] [int] IDENTITY(1,1) NOT NULL,
	[creation_script] [nvarchar](255) NULL,
	[del_cmd] [nvarchar](255) NULL,
	[description] [nvarchar](255) NULL,
	[dest_table] [sysname] NOT NULL,
	[filter] [int] NOT NULL,
	[filter_clause] [ntext] NULL,
	[ins_cmd] [nvarchar](255) NULL,
	[name] [sysname] NOT NULL,
	[objid] [int] NOT NULL,
	[pubid] [int] NOT NULL,
	[pre_creation_cmd] [tinyint] NOT NULL,
	[status] [tinyint] NOT NULL,
	[sync_objid] [int] NOT NULL,
	[type] [tinyint] NOT NULL,
	[upd_cmd] [nvarchar](255) NULL,
	[schema_option] [binary](8) NULL,
	[dest_owner] [sysname] NULL,
	[ins_scripting_proc] [int] NULL,
	[del_scripting_proc] [int] NULL,
	[upd_scripting_proc] [int] NULL,
	[custom_script] [nvarchar](2048) NULL,
	[fire_triggers_on_snapshot] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[sysarticles] ADD  DEFAULT ((0)) FOR [fire_triggers_on_snapshot]
GO
CREATE TABLE [dbo].[sysschemaarticles](
	[artid] [int] NOT NULL,
	[creation_script] [nvarchar](255) NULL,
	[description] [nvarchar](255) NULL,
	[dest_object] [sysname] NOT NULL,
	[name] [sysname] NOT NULL,
	[objid] [int] NOT NULL,
	[pubid] [int] NOT NULL,
	[pre_creation_cmd] [tinyint] NOT NULL,
	[status] [int] NOT NULL,
	[type] [tinyint] NOT NULL,
	[schema_option] [binary](8) NULL,
	[dest_owner] [sysname] NULL
) ON [PRIMARY]
SELECT artid, creation_script, del_cmd, description, dest_table, filter, filter_clause, ins_cmd, name, objid, pubid, pre_creation_cmd, status, sync_objid, type, upd_cmd, schema_option, dest_owner, ins_scripting_proc, del_scripting_proc, upd_scripting_proc, custom_script, fire_triggers_on_snapshot FROM sysarticles

UNION ALL

SELECT artid, creation_script, NULL, description, dest_object, NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL, type, NULL, schema_option, dest_owner, NULL, NULL, NULL, NULL, 0
FROM sysschemaarticles
CREATE TABLE [dbo].[syssubscriptions](
	[artid] [int] NOT NULL,
	[srvid] [smallint] NOT NULL,
	[dest_db] [sysname] NOT NULL,
	[status] [tinyint] NOT NULL,
	[sync_type] [tinyint] NOT NULL,
	[login_name] [sysname] NOT NULL,
	[subscription_type] [int] NOT NULL,
	[distribution_jobid] [binary](16) NULL,
	[timestamp] [timestamp] NOT NULL,
	[update_mode] [tinyint] NOT NULL,
	[loopback_detection] [bit] NOT NULL,
	[queued_reinit] [bit] NOT NULL,
	[nosync_type] [tinyint] NOT NULL,
	[srvname] [sysname] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[syssubscriptions] ADD  DEFAULT ((0)) FOR [queued_reinit]
GO

ALTER TABLE [dbo].[syssubscriptions] ADD  DEFAULT ((0)) FOR [nosync_type]
GO

ALTER TABLE [dbo].[syssubscriptions] ADD  DEFAULT (N'') FOR [srvname]

Restore edilen veritabanı içerisinde senkronizasyon görünüm nesneleri (SYNC ile başlayan viewler) mevcut ise restore işleminden sonra yeni replikasyonu kurmadan önce silmek gerekir. Kısa yöntem olarak aşağıdaki bir sorguyu çalıştırıp gelen sonucu script olarak çalıştırmaktır.

SELECT 'DROP VIEW '+name FROM sysobjects WHERE name LIKE 'SYNC%'

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Time limit is exhausted. Please reload CAPTCHA.