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