create procedure sys.sp_MSreinit_article
(
@publication sysname,
@article sysname = N'%',
@need_new_snapshot bit = 0,
@need_reinit_subscription bit = 0,
@force_invalidate_snapshot bit = 0,
@force_reinit_subscription bit = 0,
@check_only bit = 0,
@from_drop_publication bit = 0,
@publisher sysname = NULL,
@publisher_type sysname = N'MSSQLSERVER'
,@ignore_distributor_failure bit = 0
)
AS
BEGIN
DECLARE @retcode int,
@active tinyint,
@subscribed tinyint,
@artid int,
@pubid int,
@none tinyint,
@immediate_sync_ready bit,
@allow_anonymous bit,
@loc_publisher sysname
-- Initialize constants
SELECT @active = 2,
@subscribed = 1,
@none = 2,
@active = 2
-- 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
-- Get pub info
SELECT @immediate_sync_ready = immediate_sync_ready,
@allow_anonymous = allow_anonymous
FROM syspublications
WHERE pubid = @pubid
IF @publisher_type = N'MSSQLSERVER' AND @publisher IS NULL
BEGIN
-- Set publisher name
SELECT @loc_publisher = publishingservername()
END
ELSE
BEGIN
SELECT @loc_publisher = @publisher
END
IF @article = N'%'
BEGIN
SELECT @artid = 0
END
ELSE
BEGIN
SELECT @artid = artid
FROM sysarticles with (READPAST)
WHERE name = @article
AND pubid = @pubid
END
begin tran
save tran sp_MSreinit_article
-- If at publication level, we know that we should do it since
-- @immediate_sync_ready = 1
-- If at article level, we only do it for the articles that have been
-- processed by the snapshot agent, but not new articles.
-- sp_addarticle calls this proc at publication level.
-- It also make calls to sp_articlecolumn and sp_articleview which in turn
-- call this sp. We don't want to do anything here with those calls.
IF @need_new_snapshot = 1 AND @immediate_sync_ready = 1 AND
(@artid = 0 OR EXISTS
(
SELECT *
FROM syssubscriptions s,
sysarticles a
WHERE s.artid = a.artid
AND a.pubid = @pubid
AND s.srvid < 0
AND s.status = @active
AND s.artid = @artid
)
)
BEGIN
-- Fail and raiserror error
if @force_invalidate_snapshot = 0
begin
raiserror(20607, 16, -1)
goto UNDO
end
IF @check_only = 0
BEGIN
UPDATE syspublications
SET immediate_sync_ready = 0
WHERE pubid = @pubid
AND immediate_sync_ready <> 0
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END
DECLARE @distributor sysname,
@distribdb sysname,
@distproc nvarchar (255),
@dbname sysname
select @dbname = db_name()
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF (@retcode <> 0 OR @@ERROR <> 0) and @ignore_distributor_failure = 0
goto UNDO
IF (@distribdb IS NULL OR @distributor IS NULL) and @ignore_distributor_failure = 0
BEGIN
RAISERROR (14071, 16, -1)
goto UNDO
END
IF (@distribdb IS not NULL and @distributor IS not NULL)
BEGIN
-- Deactivate virtual (but not virtual anonymous) subscriptions at the distributor
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSinvalidate_snapshot'
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @dbname,
@publication = @publication
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END
END
-- Raise a warning. Snapshot is invalidated. Need to run
-- snapshot agent again... Only do so if the publication
-- is not being dropped.
IF @from_drop_publication = 0
RAISERROR(20605, 10, -1)
END
END
IF @need_reinit_subscription = 1
BEGIN
-- Reinitialize the subscriptions if there are any.
-- No need to reinit no_sync subscriptions.
-- The query below works for an article or whole publication (@artid == 0)
-- Including virtual subscriptions to take care anonymous.
IF EXISTS
(
SELECT *
FROM syssubscriptions s
WHERE s.status = @active
-- Only include virtual subscription if allow anonymous
AND (s.srvid >= 0 OR (@allow_anonymous = 1 AND @immediate_sync_ready = 1))
AND s.sync_type <> @none
AND (s.artid = @artid OR
(@artid = 0 AND EXISTS
(
SELECT *
FROM syspublications p,
sysarticles a
WHERE a.artid = s.artid
AND a.pubid = p.pubid
AND p.pubid = @pubid
)
)
)
)
BEGIN
-- Fail and raiserror error
IF @force_reinit_subscription = 0
BEGIN
RAISERROR(20608, 16, -1)
GOTO UNDO
END
IF @check_only = 0
BEGIN
EXEC @retcode = sys.sp_reinitsubscription @publication = @publication,
@article = @article,
@subscriber = 'all',
@for_schema_change = 1,
@publisher = @publisher
,@ignore_distributor_failure = @ignore_distributor_failure
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END
-- Raise a warning. Subscriptions is reintialized.
RAISERROR(20606, 10, -1)
END
END
END
COMMIT TRAN sp_MSreinit_article
RETURN 0
UNDO:
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN sp_MSreinit_article
COMMIT TRAN
END
RETURN 1
END