Please use FireFox to view this page

CREATE PROCEDURE sp_dropmergepublication( @publication sysname, /* The publication name */ @ignore_distributor bit = 0, @reserved bit = 0 ) AS set nocount on /* ** Declarations. */ declare @ad_guidname sysname declare @pubid uniqueidentifier declare @article sysname declare @cmd nvarchar(255) declare @retcode int declare @distproc nvarchar(300) declare @distributor sysname declare @distribdb sysname declare @working_dir varchar(255) declare @working_dir_drive varchar(255) declare @pub_dir nvarchar(255) declare @db_name sysname declare @implicit_transaction int declare @close_cursor_at_commit int declare @dynamic_filters bit declare @alt_snapshot_folder nvarchar(255) declare @pub_alt_snapshot_folder nvarchar(255) select @close_cursor_at_commit = 0 select @implicit_transaction = 0 /* ** Save setting values first before changing them */ IF (@reserved = 0) BEGIN SELECT @implicit_transaction = @@options & 2 SELECT @close_cursor_at_commit = @@options & 4 SET IMPLICIT_TRANSACTIONS OFF SET CURSOR_CLOSE_ON_COMMIT OFF END set @ad_guidname = NULL /* ** Initializations. */ select @db_name = db_name() /* ** Security Check */ EXEC @retcode = dbo.sp_MSreplcheck_publish IF @@ERROR <> 0 or @retcode <> 0 return (1) /* make sure current database is enabled for merge replication */ exec @retcode=dbo.sp_MSCheckmergereplication if @@ERROR<>0 or @retcode<>0 return (1) if LOWER(@publication) = 'all' BEGIN declare hC1 CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name FROM sysmergepublications where UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() FOR READ ONLY OPEN hC1 FETCH hC1 INTO @publication WHILE (@@fetch_status <> -1) BEGIN EXECUTE dbo.sp_dropmergepublication @publication=@publication, @ignore_distributor = @ignore_distributor, @reserved = 1 FETCH hC1 INTO @publication END CLOSE hC1 DEALLOCATE hC1 RETURN (0) END if @publication IS NULL BEGIN RAISERROR (14003, 16, -1) RETURN (1) END /* ** Get the @pubid. */ if NOT EXISTS (select * FROM sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()) BEGIN RAISERROR (20026, 16, -1, @publication) RETURN (1) END select @pubid = pubid, @dynamic_filters = @dynamic_filters, @alt_snapshot_folder = alt_snapshot_folder FROM sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name() select @ad_guidname=ad_guidname FROM sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name() /* ** Ignore external publications */ if exists (select * from sysmergesubscriptions where subid=@pubid and pubid=@pubid and db_name<>db_name()) RETURN (0) /* ** Check to make sure that there are push or pull subscriptions on the publication. */ if EXISTS (select * FROM sysmergesubscriptions subs, sysmergepublications pubs, MSmerge_replinfo repinfo WHERE pubs.name = @publication AND UPPER(pubs.publisher)=UPPER(@@servername) AND pubs.publisher_db=db_name() AND subs.pubid = pubs.pubid AND subs.status <> 2 -- Having a deleted subscription row is fine AND repinfo.repid <> @pubid AND subs.subid <> subs.partnerid) BEGIN RAISERROR (14005, 16, -1) RETURN (1) END -- drop the database role that contains users who have access to the publication -- this has to be done outside the transaction since role operations cannot be -- done in transaction declare @role sysname declare @pubidstr nvarchar(40) declare @member sysname exec dbo.sp_MSguidtostr @pubid, @pubidstr output set @role = 'MSmerge_' + @pubidstr if exists (select * from dbo.sysusers where issqlrole=1 and name=@role) begin declare role_members cursor fast_forward for select u.name as MemberName from dbo.sysusers u, dbo.sysusers g, dbo.sysmembers m where g.name = @role and g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid open role_members fetch role_members into @member while @@fetch_status <> -1 begin exec @retcode = dbo.sp_droprolemember @role, @member if (@retcode <> 0 or @@error <> 0) BEGIN close role_members deallocate role_members RAISERROR (14005, 16, -1) RETURN (1) END fetch role_members into @member end close role_members deallocate role_members exec @retcode = dbo.sp_droprole @role if (@retcode <> 0 or @@error <> 0) BEGIN RAISERROR (14005, 16, -1) RETURN (1) END end begin tran save TRANSACTION dropmergepublication /* ** delete on-demand user script, if any */ if exists (select * from sysmergeschemachange where schematype=46 and pubid=@pubid) begin exec @retcode=sp_MSremove_userscript @pubid, 1 --we may not want to do error checking here end /* ** Delete all articles from the publication. */ update sysmergepublications set snapshot_ready=0 where pubid=@pubid --so that articles can be dropped if @@ERROR<>0 goto FAILURE EXECUTE @retcode = dbo.sp_dropmergearticle @publication = @publication, @article = 'all', @ignore_distributor = @ignore_distributor if @@ERROR <> 0 OR @retcode <> 0 begin RAISERROR (20040, 16, -1, @publication) goto FAILURE end /* ** Delete sync task of Publication. */ execute @retcode = dbo.sp_MSdropmergepub_snapshot @publication = @publication, @ignore_distributor = @ignore_distributor if @@ERROR <> 0 OR @retcode <> 0 begin RAISERROR (20010, 16, -1, @publication) goto FAILURE end /* ** Remove my own subscription from sysmergesubscriptions. */ if exists (select * from sysmergesubscriptions where subid = @pubid) begin DELETE from sysmergesubscriptions WHERE subid = @pubid if @@ERROR <> 0 goto FAILURE end if exists (select * from MSmerge_replinfo where repid = @pubid) begin DELETE from MSmerge_replinfo WHERE repid = @pubid if @@ERROR <> 0 goto FAILURE end /* ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC. */ if @ignore_distributor = 0 begin /* ** Get distribution server information for remote RPC call. */ EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb = @distribdb OUTPUT, @directory = @working_dir OUTPUT IF @@ERROR <> 0 OR @retcode <> 0 BEGIN RAISERROR (14071, 16, -1) goto FAILURE END /* ** Drop the publication info from the distributor */ select @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSdrop_publication' EXECUTE @retcode = @distproc @publisher = @@SERVERNAME, @publisher_db = @db_name, @publication = @publication if @@ERROR <> 0 OR @retcode <> 0 begin goto FAILURE end /* ** If alternate snapshot folder is specified for this publication, ** try to remove the publication's snapshot folder underneath the ** the alternate snapshot location in Distributor's context */ if @alt_snapshot_folder is not null and @alt_snapshot_folder <> N'' begin /* Append publication specific folder name */ if substring(@alt_snapshot_folder,len(@alt_snapshot_folder),1)<> N'\' begin select @alt_snapshot_folder = @alt_snapshot_folder + N'\' end -- UNC version select @pub_alt_snapshot_folder = @alt_snapshot_folder + N'unc\' + fn_replcomposepublicationsnapshotfolder(@@servername,@db_name,@publication) collate database_default select @distproc = fn_replquotename(RTRIM(@distributor)) collate database_default + '.' + fn_replquotename(@distribdb) collate database_default + '.dbo.sp_MSreplremoveuncdir' -- Ignore errors as the snapshot folder may not exist at all EXECUTE @distproc @dir = @pub_alt_snapshot_folder -- FTP-enabled version select @pub_alt_snapshot_folder = @alt_snapshot_folder + N'ftp\' + fn_replcomposepublicationsnapshotfolder(@@servername,@db_name,@publication) collate database_default select @distproc = fn_replquotename(RTRIM(@distributor)) collate database_default + '.' + fn_replquotename(@distribdb) collate database_default + '.dbo.sp_MSreplremoveuncdir' -- Ignore errors as the snapshot folder may not exist at all EXECUTE @distproc @dir = @pub_alt_snapshot_folder end end /* Remove all dynamic snapshot jobs of this publication */ exec @retcode = sp_MSdropmergedynamicsnapshotjob @publication = @publication, @ignore_distributor = @ignore_distributor if @@ERROR <> 0 OR @retcode <> 0 begin goto FAILURE end /* ** Execute the cleanup routine for the publication */ exec dbo.sp_MSpublicationcleanup @publisher=@@SERVERNAME, @publisher_db = @db_name, @publication = @publication if @@ERROR <> 0 goto FAILURE DECLARE @retval INT EXECUTE @retval = master.dbo.xp_MSADEnabled if @ad_guidname is not NULL begin if @retval = 0 begin exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'DELETE', 'PUBLICATION', @publication, @db_name, @ad_guidname if @@ERROR<>0 or @retcode<>0 begin raiserror(21369, 16, -1, @publication) goto FAILURE end end else begin RAISERROR(21254, 16, -1, @publication) GOTO FAILURE end end COMMIT TRANSACTION /* ** Set back original settings */ IF @reserved = 0 BEGIN IF @implicit_transaction <>0 SET IMPLICIT_TRANSACTIONS ON IF @close_cursor_at_commit <>0 SET CURSOR_CLOSE_ON_COMMIT ON END return (0) FAILURE: RAISERROR (14006, 16, -1) /* UNDONE : This code is specific to 6.X nested transaction semantics */ if @@TRANCOUNT > 0 begin ROLLBACK TRANSACTION dropmergepublication COMMIT TRANSACTION end /* ** Set back original settings */ IF @reserved = 0 BEGIN IF @implicit_transaction <>0 SET IMPLICIT_TRANSACTIONS ON IF @close_cursor_at_commit <>0 SET CURSOR_CLOSE_ON_COMMIT ON END RETURN (1)