Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MSdeletelightweightsubscription
	@publication_id			uniqueidentifier
	declare @artid			uniqueidentifier
	declare @retcode		int
	declare @artnick		int
	declare @delbatchsize	int
	declare @delcount 		int
	declare @objid			int
	declare @publisher 		sysname
	declare @publisher_db	sysname
	declare @publication_name sysname
	declare @preserve_rowguidcol bit
	declare @qualified_tablename nvarchar(517)

	select  @publisher= publisher,
			@publisher_db= publisher_db,
			@publication_name= name
		from dbo.sysmergepublications
		where pubid = @publication_id

	-- iterate over all articles of that publication
	declare article_curs cursor local fast_forward for
		select distinct artid from dbo.sysmergearticles
			where pubid = @publication_id
		for read only

	begin tran
	save tran tran_deletesubscription_lws
	open article_curs
	fetch next from article_curs into @artid

	while (@@fetch_status <> -1)
		-- Delete the article procs.
		exec @retcode= sys.sp_MSdroplightweightarticleprocs @pubid=@publication_id, @artid=@artid
		if @@error<>0 or @retcode<>0 goto FailureDropSub
		-- If the current article does not belong to another publication,
		-- remove the triggers, the MSmerge_rowtrack entries, the MSmerge_idrange entry,
		-- the article-specific conflict table, conflict info.
		if 1 = (select count(*) from dbo.sysmergearticles where artid = @artid)
            -- set the article status to indicate it is in no other publication,
            -- and it is about to be removed.
            update dbo.sysmergearticles set status= 7
                where pubid = @publication_id and artid = @artid

			exec @retcode= sys.sp_MSdroplightweighttriggers @artid=@artid
			if @@error<>0 or @retcode<>0 goto FailureDropSub

			-- remove the MSmerge_rowtrack entries
			select @artnick= nickname from dbo.sysmergearticles where artid = @artid
			set @delbatchsize= 5000
			set @delcount= @delbatchsize
			while @delcount = @delbatchsize
				delete top (@delbatchsize) from dbo.MSmerge_rowtrack where tablenick = @artnick
				set @delcount= @@rowcount

			-- remove the entry in MSmerge_idrange
			set @objid= (select objid from dbo.sysmergearticles where artid = @artid)
			if @objid is not null and
			   1 = (select count(*) from dbo.sysmergearticles where objid = @objid)
				delete from dbo.MSmerge_idrange where objid = @objid

			-- Drop the article-specific conflict table.
			exec @retcode= sys.sp_MSdrop_article_conflict_table @pubid=@publication_id, @artid=@artid
			if @@error<>0 or @retcode<>0 return 1

			-- Delete conflict info.
			if object_id('MSmerge_conflicts_info') is not NULL
				delete from dbo.MSmerge_conflicts_info where tablenick = @artnick

			-- Drop the rowguidcol if it was created for replication only.
			select @preserve_rowguidcol= preserve_rowguidcol
			    from dbo.sysmergearticles
			    where pubid=@publication_id and artid=@artid

            if 0=@preserve_rowguidcol
		-- presever rowguidcol if the table has filestream column
	       and not exists (select * from sys.columns where object_id = @objid and is_filestream = 1)
                select @qualified_tablename= quotename(schema_name(schema_id)) + '.' + quotename(name) from sys.objects where object_id = @objid
                if @@error<>0 or @qualified_tablename is null return 1

                exec @retcode= sys.sp_MSdropguidcolumn @tablename=@qualified_tablename
                if @@error<>0 or @retcode<>0 return 1

		-- Remove the entry in MSmerge_filteringcolumns_lightweight
		delete from dbo.MSmerge_filteringcolumns_lightweight
			where publication_id = @publication_id and tablenick = @artnick
		-- Remove the entry in sysmergearticles
		delete from dbo.sysmergearticles
			where pubid = @publication_id and artid = @artid

		-- get next article
		fetch next from article_curs into @artid

	close article_curs
	deallocate article_curs

	-- iterate over all schema articles of that publication
	declare schemaarticle_curs cursor local fast_forward for
		select distinct artid from dbo.sysmergeschemaarticles
			where pubid = @publication_id
		for read only

	open schemaarticle_curs
	fetch next from schemaarticle_curs into @artid

	while (@@fetch_status <> -1)
		-- Remove the entry in sysmergeschemaarticles
		delete from dbo.sysmergeschemaarticles
			where pubid = @publication_id and artid = @artid
		fetch next from schemaarticle_curs into @artid

	close schemaarticle_curs
	deallocate schemaarticle_curs

	-- remove the subscription
	delete from dbo.sysmergepublications where pubid = @publication_id

	-- delete supportability settings for the subscriptions that we are about to delete.
    if object_id('MSmerge_supportability_settings','U') is not NULL
		delete from dbo.MSmerge_supportability_settings where pubid = @publication_id       	
		delete from dbo.MSmerge_log_files where pubid = @publication_id

	delete from dbo.sysmergesubscriptions where pubid = @publication_id
	if @@error <> 0 goto FailureDropSub

	-- If the only remaining subscriptions were made before the last restore,
	-- we remove them now.
	if not exists (select * from dbo.sysmergesubscriptions
						where status <> 7) -- REPLICA_STATUS_BeforeRestore
		delete from dbo.sysmergesubscriptions
		truncate table dbo.MSmerge_supportability_settings
		truncate table dbo.MSmerge_log_files
		if @@error <> 0 goto FailureDropSub
	commit tran
	return 0

	raiserror(14056, 16, -1)
	goto Failure
	rollback tran tran_deletesubscription_lws
	commit tran
	close article_curs
	deallocate article_curs
	return 1

Last revision 2008RTM
See also

  sp_MSpublicationcleanup (Procedure)
sp_MSremovedbreplication (Procedure)


  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash