Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHdroparticle

  No additional text.


Syntax


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

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_droparticle (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash