-- Name:
-- sp_MSrepl_droparticle
-- Description:
-- Internal proc for executing the logic of drop article. Executed in
-- the context of the publisher even if it is the distributor for HREPL.
-- Security:
-- Internal
-- Requires Certificate signature for catalog access
-- Returns:
-- Success (0) or failure (1)
-- Owner:
--
create procedure sys.sp_MSrepl_droparticle
(
@publication sysname,
@article sysname,
@ignore_distributor bit,
@force_invalidate_snapshot bit,
@publisher sysname,
@from_drop_publication bit,
@publisher_type sysname,
@internal bit = 0
)
AS
BEGIN
/*
** Declarations.
*/
DECLARE @cmd nvarchar(4000)
DECLARE @objid int
DECLARE @qualname nvarchar(517)
DECLARE @pubid int
DECLARE @publish_bit smallint
DECLARE @retcode int
DECLARE @filter_name nvarchar(517)
DECLARE @view_name nvarchar(517)
DECLARE @type tinyint
DECLARE @procnum smallint
DECLARE @virtual_id smallint
DECLARE @push tinyint
DECLARE @distributor sysname
DECLARE @distribdb sysname
DECLARE @distproc nvarchar (255)
-- SyncTran
DECLARE @allow_sync_tran_id bit
DECLARE @allow_queued_tran_id bit
declare @artid int, @insproc_id int, @updproc_id int, @delproc_id int, @updtrig_id int
declare @filter_id int
declare @view_id int
declare @tran_conflict_tabid int
declare @tran_conflict_procid int
-- these are not use but required by sys.sp_IHgetPublisherInfo
declare @autogen_sync_procs_id bit
declare @sync_method tinyint
declare @allow_sync_tran bit
declare @allow_queued_tran bit
declare @allow_dts bit
declare @repl_freq tinyint
DECLARE @publisher_db sysname
DECLARE @tablename sysname
DECLARE @owner sysname -- for recursive call to sp_droparticle we need to original @publisher
DECLARE @publisher_id int
DECLARE @table_id int
DECLARE @hrepl bit
DECLARE @options int
,@publishingservername sysname
DECLARE @distributortimestamp datetime
DECLARE @OPT_ENABLED_FOR_P2P int
,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
SELECT @OPT_ENABLED_FOR_P2P = 0x1
,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
SET NOCOUNT ON
/*
** Initializations.
*/
SELECT @virtual_id = -1 /* Const: virtual subscriber id */
SELECT @publish_bit = 1
SELECT @hrepl = 1
/*
** Security Check.
*/
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
-- Check to see if database is activated for publication
IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
/*
** Parameter Check: @publication.
** The @publication name must conform to the rules for identifiers.
*/
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_droparticle')
RETURN (1)
END
-- Verify publication exists
SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
IF (@pubid IS NULL)
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END
/*
** Parameter Check: @ignore_distributor
** HREPL: Not supported
*/
IF NOT @publisher_type = N'MSSQLSERVER' and @ignore_distributor <> 0
BEGIN
RAISERROR (21608, 16, -1)
RETURN (1)
END
/*
** Get the @pubid.
*/
-- SyncTran
--SELECT @pubid = pubid FROM syspublications WHERE name = @publication
IF @publisher_type = N'MSSQLSERVER'
BEGIN
SELECT @pubid = pubid,
@allow_sync_tran_id = allow_sync_tran,
@allow_queued_tran_id = allow_queued_tran,
@options = options
FROM syspublications
WHERE name = @publication
END
ELSE
BEGIN
EXEC @retcode = sys.sp_IHgetPublicationInfo
@publisher = @publisher,
@publication = @publication,
@publication_id = @pubid OUTPUT,
@autogen_sync_procs_id = @autogen_sync_procs_id OUTPUT,
@sync_method = @sync_method OUTPUT,
@allow_sync_tran = @allow_sync_tran OUTPUT,
@allow_queued_tran = @allow_queued_tran OUTPUT,
@allow_dts = @allow_dts OUTPUT,
@repl_freq = @repl_freq OUTPUT
IF @@ERROR <> 0 AND @retcode <> 0
RETURN (1)
IF @pubid IS NULL
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END
END
/*
** Parameter Check: @article.
** If the @article is 'all', drop all articles for the specified
** publication (@publication).
*/
IF LOWER(@article) = 'all'
BEGIN
IF @publisher_type = N'MSSQLSERVER'
BEGIN
DECLARE hCart CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT name, artid
FROM sysextendedarticlesview
WHERE pubid = @pubid
ORDER BY artid DESC
END
ELSE
BEGIN
DECLARE hCart CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT article, 0
FROM IHextendedArticleView
WHERE publication_id = @pubid
END
-- If drop all articles, set force flag to true
select @force_invalidate_snapshot = 1
OPEN hCart
FETCH hCart INTO @article, @artid
WHILE (@@fetch_status <> -1)
BEGIN
EXEC @retcode = sys.sp_MSrepl_droparticle @publication = @publication,
@article = @article,
@ignore_distributor = @ignore_distributor,
@force_invalidate_snapshot = @force_invalidate_snapshot,
@publisher = @publisher,
@from_drop_publication = @from_drop_publication,
@publisher_type = @publisher_type,
@internal = 1
IF (@retcode > 1)
BEGIN
-- HREPL failure - ignore until the end
SET @hrepl = 0
END
ELSE IF @@ERROR <> 0 or @retcode <> 0
BEGIN
RETURN (1)
END
FETCH hCart INTO @article, @artid
END
CLOSE hCart
DEALLOCATE hCart
GOTO PROCEXIT
END
/*
** Parameter Check: @article.
** The @article name must conform to the rules for identifiers.
*/
IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_droparticle')
RETURN (1)
END
/*
EXECUTE @retcode = sys.sp_validname @article
IF @retcode <> 0
RETURN (1)
*/
/*
** Ascertain the existence of the article.
*/
IF NOT EXISTS (SELECT *
FROM sysextendedarticlesview
WHERE name = @article
AND pubid = @pubid)
BEGIN
RAISERROR (20027, 11, -1, @article)
RETURN (1)
END
/*
** Check to make sure that there are no 'real' subscriptions on the article.
*/
IF @publisher_type = N'MSSQLSERVER'
BEGIN
IF EXISTS (SELECT *
FROM syssubscriptions, sysextendedarticlesview
WHERE sysextendedarticlesview.name = @article
AND sysextendedarticlesview.pubid = @pubid
AND sysextendedarticlesview.artid = syssubscriptions.artid
AND syssubscriptions.srvid <> @virtual_id)
BEGIN
RAISERROR (14046, 16, -1)
RETURN (1)
END
END
-- If SyncTran/QueuedTran enabled
-- retrieve info from sysarticle updates
if (@allow_sync_tran_id = 1 or @allow_queued_tran_id = 1)
begin
-- HetPubs do not support updatable subscribers, these should always be 0
IF @publisher_type = N'MSSQLSERVER'
BEGIN
select @artid = artid from sysarticles where name = @article and pubid = @pubid
select @insproc_id = sync_ins_proc, @updproc_id = sync_upd_proc, @delproc_id = sync_del_proc,
@updtrig_id = sync_upd_trig,
@tran_conflict_tabid = conflict_tableid,
@tran_conflict_procid = ins_conflict_proc
from sysarticleupdates
where artid = @artid and pubid = @pubid
END
ELSE
BEGIN
raiserror(21609, 16, -1)
return (1)
END
end
-- end SyncTran
/*
** Retrieve the object id of the underlying object,
** article id, and article type. Note that the
** subsequent code relies on the values of the variables in
** the select list. Please do not remove any variable from
** the seletc list unless you make sure that all the subsequent
** references to the variable are accounted for.
*/
IF @publisher_type = N'MSSQLSERVER'
BEGIN
SELECT @artid = artid, @objid = objid, @type = type
FROM sysextendedarticlesview
WHERE name = @article
AND pubid = @pubid
END
ELSE
BEGIN
SELECT @artid = a.article_id,
@table_id = a.table_id,
@type = a.type,
@objid = a.objid,
@publisher_id = a.publisher_id,
@tablename = m.source_object,
@owner = m.source_owner
FROM MSarticles m
JOIN IHarticles a
ON m.article_id = a.article_id
AND a.publication_id = m.publication_id
WHERE a.name = @article
AND a.publication_id = @pubid
END
begin tran
save TRAN droparticle
-- @ignore_distributor is set to 1 when removing replication forcefully. In that
-- case, no need to check or reinit
if @ignore_distributor = 0
begin
-- Have to call this stored procedure to invalidate existing snapshot
-- if there are any. immediate_sync_ready bit would be changed or error will be railsed.
-- for Sql publishers execute on publisher db, for het pubs go to distribution db
EXECUTE @retcode = sys.sp_MSreinit_article
@publication = @publication,
@need_new_snapshot = 1,
@force_invalidate_snapshot = @force_invalidate_snapshot,
@from_drop_publication = @from_drop_publication,
@publisher = @publisher,
@publisher_type = @publisher_type
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
end
-- Drop virtual subscription first for @immediate_sync publications
IF EXISTS
(
SELECT *
FROM syspublications sysp
JOIN sysextendedarticlesview sysea
on sysp.pubid = sysea.pubid
JOIN syssubscriptions syss
on sysea.artid = syss.artid
WHERE sysp.name = @publication
AND sysp.pubid = @pubid
AND sysp.immediate_sync = 1
AND syss.srvid = -1
)
BEGIN
EXECUTE @retcode = sys.sp_dropsubscription
@publication = @publication,
@article = @article,
@subscriber = NULL,
@ignore_distributor = @ignore_distributor,
@reserved = 'internal',
@publisher = @publisher
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
IF @@trancount > 0
BEGIN
ROLLBACK TRAN droparticle
COMMIT TRAN
END
RETURN (1)
END
END
/* Drop article at the distributor side */
IF NOT @publisher_type = N'MSSQLSERVER'
BEGIN
SELECT @publisher_db = publisher_db from dbo.MSpublications
WHERE @publication = publication
AND @publisher_id = publisher_id
EXEC @retcode = sys.sp_IHdroparticle
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
IF @@trancount > 0
BEGIN
ROLLBACK TRAN droparticle
COMMIT TRAN
END
RETURN (1)
END
END
/*
** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
*/
if @ignore_distributor = 0
begin
if @publisher_type = N'MSSQLSERVER'
BEGIN
EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
if @@trancount > 0
ROLLBACK TRAN
RETURN (1)
END
SELECT @publisher_db = DB_NAME()
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
'.dbo.sp_MSdrop_article'
,@publishingservername = publishingservername()
EXECUTE @retcode = @distproc
@publisher = @publishingservername,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
if @@trancount > 0
ROLLBACK TRAN
RETURN (1)
END
END
ELSE
BEGIN
EXECUTE @retcode = dbo.sp_MSdrop_article
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
if @@trancount > 0
ROLLBACK TRAN
RETURN (1)
END
END
end
IF @type IN (0x20, 0x40, 0x60, 0xA0, 0x80)
BEGIN
IF @publisher_type = N'MSSQLSERVER'
BEGIN
-- Handle the schema only articles a little bit differently from
-- other articles as they are simpler objects.
-- Note that we have already obtained the article id earlier
-- so we can use that to delete the corresponding record
-- in sysschemaarticles
DELETE sysschemaarticles WHERE artid = @artid and pubid = @pubid
-- If the object is no longer published as a schema only
-- article, unmark its published for schema only bit (512) in
-- sys.objects/replinfo so that it can be dropped by the user.
-- Note that we need to check sysmergeschemaarticles too.
-- Note that we have obtained the object id for the undelying
-- object of this article already.
IF NOT EXISTS (SELECT *
FROM sysschemaarticles
WHERE objid = @objid)
BEGIN
EXEC sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
IF NOT (@qualname IS NULL)
BEGIN
EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
IF @@ERROR = 0
BEGIN
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE name = 'sysmergeschemaarticles')
BEGIN
EXEC %%Object(ID = @objid).SetSchemaPublished(Value = 0)
END
ELSE IF NOT EXISTS (SELECT *
FROM sysmergeschemaarticles
WHERE objid = @objid)
BEGIN
EXEC %%Object(ID = @objid).SetSchemaPublished(Value = 0)
END
END
END
END
END
END
ELSE
BEGIN
/*
** Delete article from sysarticles and clear publish bit in
** sys.objects.
*/
/*
** If this article is the only one that references this object,
** then we can safely turn off the publish bit in sys.objects.
*/
IF @publisher_type = N'MSSQLSERVER'
BEGIN
IF NOT EXISTS (SELECT *
FROM sysarticles
WHERE objid = @objid
AND NOT (name = @article AND pubid = @pubid))
BEGIN
declare @cdc_tracked_tables table (object_id int)
if object_id('cdc.change_tables') is not null
begin
insert @cdc_tracked_tables select distinct source_object_id from cdc.change_tables
end
DECLARE @obj_tmp int
SELECT @obj_tmp = objid FROM sysarticles
WHERE name = @article AND pubid = @pubid
IF NOT (@obj_tmp IS NULL)
BEGIN
EXEC sys.sp_MSget_qualified_name @obj_tmp, @qualname OUTPUT
IF NOT (@qualname IS NULL)
BEGIN
EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
IF @@ERROR = 0
EXEC %%Object(ID = @obj_tmp).SetPublished(Value = 0)
-- Clear the "Replicated" bits if the published
-- object that does not have any active/initiated
-- subscriptions and is not published in a
-- publication that allows is enabled for autonosyncs
IF -- No active subscriptions
NOT EXISTS (SELECT * FROM dbo.syssubscriptions WHERE
artid in (SELECT sa.artid
FROM dbo.sysextendedarticlesview sa
INNER JOIN dbo.syspublications sp
ON sa.pubid = sp.pubid
WHERE NOT (sa.name = @article AND sa.pubid = @pubid) -- Don't consider the current article
AND sa.objid = @obj_tmp
AND sp.repl_freq = 0)
AND status in (2,3)) AND
-- Not published in a allow is enabled for autonosyncs publication
NOT EXISTS (SELECT *
FROM dbo.sysextendedarticlesview sa
INNER JOIN dbo.syspublications sp
ON sa.pubid = sp.pubid
WHERE NOT (sa.name = @article AND sa.pubid = @pubid) -- Don't consdier the current article
AND sa.objid = @obj_tmp
AND sp.allow_initialize_from_backup = 1)
AND NOT EXISTS (SELECT *
FROM @cdc_tracked_tables where object_id = @obj_tmp)
BEGIN
-- Call different system object methods depending object type
IF OBJECTPROPERTY(@obj_tmp, 'IsProcedure') = 1
BEGIN
EXEC %%Module(ID = @obj_tmp).SetProcReplicated(Value = 0)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
EXEC %%Module(ID = @obj_tmp).SetProcReplSerialOnly(Value = 0)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
END
ELSE
BEGIN
EXEC %%Relation(ID = @obj_tmp).SetReplicated(Value = 0)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
-- in case the p2p conflict detection is enabled
if exists(select * from syspublications
where name = @publication and (options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION)
begin
--set the table persistent property replpeerid to 0, the table was already x-locked
--hidden column $p2pversion is removed internally
EXEC %%Relation(ID = @obj_tmp).SetReplPeerId(Value = 0)
if @@ERROR <> 0
begin
select @retcode = 1
goto UNDO
end
--drop the conflict table
declare @conflicttablename nvarchar(1000)
exec sp_MSgetpeerconflictname @prefix = N'conflict', @tabid = @obj_tmp, @peerconflictname = @conflicttablename output
select @conflicttablename = N'[dbo].' + QUOTENAME(@conflicttablename)
if(object_id(@conflicttablename, N'U') is not null)
begin
select @cmd = N'drop table ' + @conflicttablename
exec (@cmd)
if @@ERROR <> 0
begin
select @retcode = 1
goto UNDO
end
end
end --in case the p2p conflict detection is enabled
END
END
END
END
/*
EXEC (@cmd)
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
ROLLBACK TRAN
RAISERROR (14047, 16, -1, @article)
RETURN (1)
END
*/
END
/*
** Drop article view if not logbased manualview (type = 5)
*/
IF (@type & 5) = 1
BEGIN
SELECT @view_id = so.object_id
FROM sysarticles as sa join sys.objects as so
ON sa.sync_objid = so.object_id
WHERE sa.name = @article
AND sa.pubid = @pubid
AND so.type = 'V'
exec sys.sp_MSget_qualified_name @view_id, @view_name OUTPUT
END
/*
** Drop article filter if not logbased manualfilter (type = 3)
*/
IF (@type & 3) = 1
BEGIN
SELECT @filter_id = so.object_id
FROM sysarticles as sa join sys.objects as so
ON sa.filter = so.object_id
WHERE sa.name = @article
AND pubid = @pubid
AND so.type = 'RF'
exec sys.sp_MSget_qualified_name @filter_id, @filter_name OUTPUT
END
IF( @type & 3 ) = 3
BEGIN
select @filter_id = filter from sysarticles
where name = @article and pubid = @pubid
exec sys.sp_MSget_qualified_name @filter_id, @filter_name OUTPUT
if @filter_name is not null
EXEC sys.sp_MSsetfilterparent @filter_name, 0
-- Clear base table dependency on the filter
EXEC sys.sp_MSsetfilteredstatus @objid
-- This is a manual filter, we should not drop it automatically
-- since it is not created by us.
-- Set @filter_id to null so the object will not be dropped later.
select @filter_name = null
END
/*
** If this is a table based article, Drop all article columns.
** This is done to force all Text\Image column status to be updated.
*/
IF (@type & 8) != 8
BEGIN
-- propagate @ignore_distributor to sp_articlecolumn to allow forced cleanup
EXECUTE @retcode = sys.sp_articlecolumn
@publication,
@article,
@operation = 'drop',
@ignore_distributor = @ignore_distributor,
-- synctran
@refresh_synctran_procs = 0,
@force_invalidate_snapshot = @force_invalidate_snapshot
, @internal = 1
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRAN droparticle
commit tran
end
RETURN (1)
END
END
END
/*
** Remove the row from sysarticles.
*/
IF @publisher_type = N'MSSQLSERVER'
BEGIN
DELETE
FROM sysarticles
WHERE name = @article
AND pubid = @pubid
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
ROLLBACK TRAN
RAISERROR (14047, 16, -1, @article)
RETURN (1)
END
END
ELSE
BEGIN
DELETE FROM dbo.IHarticles
WHERE name = @article
AND publication_id = @pubid
IF @@ERROR <> 0
BEGIN
RAISERROR (14047, 16, -1, @article)
RETURN (1)
END
END
-- SyncTran
/*
** Drop associated sync tran procs and entries in sysarticle updates
** HetPubs do not support updatable subscribers so these willl alwasy be 0
*/
if (@allow_sync_tran_id = 1 or @allow_queued_tran_id = 1)
begin
exec @retcode = sys.sp_MSdrop_object
@object_id = @insproc_id
if @retcode <> 0 or @@error <> 0
goto UNDO
exec @retcode = sys.sp_MSdrop_object
@object_id = @updproc_id
if @retcode <> 0 or @@error <> 0
goto UNDO
exec @retcode = sys.sp_MSdrop_object
@object_id = @delproc_id
if @retcode <> 0 or @@error <> 0
goto UNDO
if @updtrig_id is not null
begin
exec @retcode = sys.sp_MSdrop_object
@object_id = @updtrig_id
if @retcode <> 0 or @@error <> 0
goto UNDO
end
-- drop conflict tables as necessary
if @tran_conflict_tabid is not null
begin
exec @retcode = sys.sp_MSdrop_object
@object_id = @tran_conflict_tabid
if @retcode <> 0 or @@error <> 0
goto UNDO
end
if @tran_conflict_procid is not null
begin
exec @retcode = sys.sp_MSdrop_object
@object_id = @tran_conflict_procid
if @retcode <> 0 or @@error <> 0
goto UNDO
end
delete from sysarticleupdates where artid = @artid and pubid = @pubid
if @@ERROR <> 0
begin
if @@trancount > 0
ROLLBACK TRAN
RETURN (1)
end
-- Cleanup MSpub_identity_range if needed.
if not exists (select * from sysarticles where objid = @objid)
begin
if exists (select * from MSpub_identity_range where objid = @objid)
begin
-- Drop the identity range constraits.
-- RESEED and change constraint
exec @retcode = sys.sp_MSreseed
@objid = @objid,
-- next_seed and range can be anything.
@next_seed = 10,
@range = 10,
@is_publisher = -1,
@check_only = 1,
@drop_only = 1
IF @retcode <> 0 or @@ERROR <> 0
GOTO UNDO
delete MSpub_identity_range where objid = @objid
if @@ERROR <> 0
GOTO UNDO
end
end
-- end SyncTran
end
end
IF @publisher_type = N'MSSQLSERVER'
BEGIN
-- if this publication is p2p then we will need to attempt to
-- detect invalid configurations at the peer node to avoid
-- disjoint article sets. NOTE that the proc will also help
-- delete MSsubscription_articles meta-data at the subscriber node
IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
BEGIN
DECLARE @min_artid int
-- since the article is dropped we need the min
-- article id to post the command... if none left
-- then we will skip over the post completely...
SELECT @min_artid = MIN(artid)
FROM sysextendedarticlesview
WHERE pubid = @pubid
IF @min_artid IS NOT NULL
BEGIN
SELECT @cmd = N'if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSdetectinvalidpeersubscription @publisher=N' + QUOTENAME(publishingservername(), N'''') +
N',@publisher_db=N' + QUOTENAME(@publisher_db, N'''') +
N',@publication=N' + QUOTENAME(@publication, N'''') +
N',@article=N' + QUOTENAME(@article, N'''') +
N',@dest_table=N' + QUOTENAME(OBJECT_NAME(@objid), N'''') +
N',@dest_owner=N' + QUOTENAME(SCHEMA_NAME(OBJECTPROPERTY(@objid,'SchemaId')), N'''') +
N',@type=''DEL''' +
N' end'
EXEC @retcode = sys.sp_replpostcmd 0, @pubid, @min_artid, 1, @cmd
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
END
END
IF @publisher_type LIKE N'ORACLE%'
BEGIN
-- Update distributor timestamp to reflect change in meta data
SET @distributortimestamp = GETDATE()
UPDATE dbo.IHpublishers
SET flush_request_time = @distributortimestamp
WHERE publisher_id = @publisher_id
IF @@error <> 0
BEGIN
GOTO UNDO
END
END
COMMIT TRAN
-- Remove the trigger and delete the tracking table at the publisher if the table is not
-- associated with any published articles.
-- The SP will check for other publications using the article before deleting it
IF @publisher_type LIKE N'ORACLE%'
BEGIN
DECLARE @article_view nvarchar(256)
EXEC @article_view = sys.fn_IHview_name @artid, @table_id
IF @@error <> 0
BEGIN
RAISERROR (21777, 11, -1, @article)
GOTO UNDO
END
EXEC @retcode = sys.sp_ORAdroparticle @publisher,
@artid,
@publisher_id,
@table_id,
@owner,
@tablename,
@article_view,
@distributortimestamp
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
-- Report that the HREPL info at the publisher was not dropped
-- but do not prevent article drop from finishing.
SET @hrepl = 0
SET @retcode = 0
END
END
IF @view_name IS NOT NULL
BEGIN
-- @view_name is already quoted.
SELECT @cmd = 'drop view ' + @view_name
exec (@cmd)
END
IF @filter_name IS NOT NULL
BEGIN
-- @filter_name is already quoted.
SELECT @cmd = 'drop proc ' + @filter_name
exec (@cmd)
END
/*
** Force the article cache to be refreshed; only if needed
*/
if ( @ignore_distributor = 0 )
EXECUTE sys.sp_replflush
PROCEXIT:
-- Override status and return an error if HREPL failed.
-- Post-hrepl steps will have completed and left the distributor
-- in a consistent state.
-- SPECIAL CASE: If the internal flag is set, this is part of a drop all
-- articles. Hold until the end by passing back @retcode > 1
IF @hrepl = 0 AND @internal = 0
BEGIN
RAISERROR(21748, 16, -1, @publisher)
SET @retcode = 1
END
ELSE IF @hrepl = 0 AND @internal = 1
BEGIN
SET @retcode = 2
END
RETURN (@retcode)
UNDO:
if @@trancount > 0
begin
ROLLBACK TRANSACTION droparticle
commit tran
end
RETURN (1)
END
