-- Name:
-- sp_IHdroparticle
-- Description:
-- HREPL specific logic for dropping an article
-- Security:
-- Internal
-- Requires Certificate signature for catalog access
-- Returns:
-- Success (0) or failure (1)
-- Owner:
--
-- Notes:
-- This SP is designed to be called by sp_droparticle. It should not be called stand-alone.
-- It is also called in sp_addarticle if the SP fails after sp_IHaddarticle completes successfully.
-- This routine will only remove the article the heterogenous tables. sp_MSdroparticle must be called
-- to complete removal of the article. Pass either the article name or ID. If @article_ID is NULL
-- then @article is used to look it up.
CREATE PROCEDURE sys.sp_IHdroparticle
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname
)
AS
BEGIN
DECLARE @publisher_id int,
@article_id int,
@source_owner sysname,
@source_table sysname,
@publication_id int,
@retcode int
-- Parameter check: publisher
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_IHdroparticle')
RETURN (1)
END
EXECUTE @retcode = dbo.sp_validname @publisher
IF @retcode <> 0
RETURN (1)
-- Look up publisher ID
SELECT @publisher_id = srvid
FROM master..sysservers
WHERE UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default
IF @@ERROR <> 0
RETURN (1)
IF @publisher_id is NULL
BEGIN
raiserror(14080, 16, -1, @publisher)
return (1)
END
-- Parameter check: publication
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_IHdroparticle')
RETURN (1)
END
-- Look up publication ID
SELECT @publication_id = publication_id
FROM MSpublications
WHERE publication = @publication
AND publisher_id = @publisher_id
IF @@ERROR <> 0
RETURN (1)
IF @publication_id is NULL
BEGIN
raiserror(20026, 16, -1, @publication)
return (1)
END
-- Parameter check: article
IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_IHdroparticle')
RETURN (1)
END
SELECT @article_id = iha.article_id,
@source_owner = ihpt.owner,
@source_table = ihpt.name
FROM IHarticles iha,
IHpublishertables ihpt
WHERE iha.table_id = ihpt.table_id
AND iha.name = @article
AND publication_id = @publication_id
IF @@ERROR <> 0
RETURN (1)
IF @article_id is NULL
BEGIN
raiserror(20027, 16, -1, @article)
return (1)
END
-- Delete the published columns
DELETE IHcolumns
FROM IHcolumns ihc
JOIN IHpublishercolumns ihpc ON ihc.publishercolumn_id = ihpc.publishercolumn_id
WHERE ihpc.publisher_id = @publisher_id
AND article_id = @article_id
IF @@ERROR <> 0
RETURN (1)
-- Delete the article
DELETE IHarticles
FROM IHarticles ih
JOIN MSpublications mspub ON ih.publication_id = mspub.publication_id
WHERE mspub.publisher_id = @publisher_id
AND article_id = @article_id
IF @@ERROR <> 0
RETURN (1)
-- Delete publisher information if the table is not used by any other articles
EXEC @retcode = sp_IHflushmetadata @publisher = @publisher,
@source_owner = @source_owner,
@source_table = @source_table
IF @@ERROR != 0 OR @retcode != 0
BEGIN
RETURN (1)
END
-- Update the timestamp in IHpublishers entry to indicate a meta data change
UPDATE dbo.IHpublishers
SET flush_request_time = GETDATE()
WHERE publisher_id = @publisher_id
IF @@ERROR <> 0
RETURN (1)
END