Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdeletelightweightsubscription

  No additional text.


Syntax
create procedure sys.sp_MSdeletelightweightsubscription
	@publication_id			uniqueidentifier
as
	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)
	begin
		-- 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)
		begin
            -- 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
			begin
				delete top (@delbatchsize) from dbo.MSmerge_rowtrack where tablenick = @artnick
				set @delcount= @@rowcount
			end

			-- 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)
			begin
				delete from dbo.MSmerge_idrange where objid = @objid
			end

			-- 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
			begin
				delete from dbo.MSmerge_conflicts_info where tablenick = @artnick
			end

			-- 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)
            begin
                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
            end
		end

		-- 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
	end

	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)
	begin
		-- Remove the entry in sysmergeschemaarticles
		delete from dbo.sysmergeschemaarticles
			where pubid = @publication_id and artid = @artid
		fetch next from schemaarticle_curs into @artid
	end

	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
    begin
		delete from dbo.MSmerge_supportability_settings where pubid = @publication_id       	
		delete from dbo.MSmerge_log_files where pubid = @publication_id
	end

	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
	begin
		delete from dbo.sysmergesubscriptions
		truncate table dbo.MSmerge_supportability_settings
		truncate table dbo.MSmerge_log_files
		if @@error <> 0 goto FailureDropSub
	end
	
	commit tran
	return 0

FailureDropSub:
	raiserror(14056, 16, -1)
	goto Failure
	
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)
       



News

  Query a named instance
Sybase+ASE+silent+install
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
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash