The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_IHarticlecolumn(nvarchar @publication, nvarchar @article
, nvarchar @column
, nvarchar @operation
, bit @refresh_synctran_procs
, bit @ignore_distributor
, int @change_active
, bit @force_invalidate_snapshot
, bit @force_reinit_subscription
, nvarchar @publisher
, nvarchar @publisher_type
, nvarchar @publisher_dbms
, nvarchar @publisher_version)
MetaData:
CREATE PROCEDURE sys.sp_IHarticlecolumn ( @publication sysname, @article sysname, @column sysname = NULL, @operation nvarchar(4) = N'add', -- Add or delete a column @refresh_synctran_procs bit = 1, @ignore_distributor bit = 0, @change_active int = 0, @force_invalidate_snapshot bit = 0, @force_reinit_subscription bit = 0, @publisher sysname, @publisher_type sysname, @publisher_dbms sysname, @publisher_version sysname ) AS BEGIN declare @article_id int declare @table_id int declare @publication_type int declare @dest_type_name sysname declare @dest_type sysname declare @dest_length bigint declare @dest_prec int declare @dest_scale int declare @dest_nullable bit declare @dataloss bit declare @src_type sysname declare @src_length bigint declare @src_prec int declare @src_scale int declare @src_nullable bit declare @column_ordinal int declare @publishercolumn_id int declare @retcode int declare @count int declare @artcolumn sysname declare @ispk int declare @columnid int declare @publisher_id int DECLARE @coldatamap TABLE ( column_name sysname, column_ordinal int, dest_datatype sysname, dest_length bigint, dest_precision bigint, dest_scale int, dest_nullable bit, dataloss bit ) SET NOCOUNT ON -- -- Security Check -- exec @retcode = sys.sp_MSreplcheck_publish if @@ERROR <> 0 or @retcode <> 0 return(1) -- -- Parameter Check: @publication. -- Make sure that the publication exists and that it conforms to the -- rules for identifiers. -- IF @publication IS NULL BEGIN RAISERROR (14043, 16, -1, N'@publication', 'sp_IHarticlecolumn') RETURN (1) END EXECUTE @retcode = dbo.sp_validname @publication IF @retcode <> 0 RETURN (1) -- -- Parameter Check: @article. -- Check to make sure that the article exists in the publication. -- IF @article IS NULL BEGIN RAISERROR (14043, 16, -1, N'@article', 'sp_IHarticlecolumn') RETURN (1) END -- Get table ID to improve perf of other calls SELECT @article_id = a.article_id, @table_id = a.table_id, @publication_type = p.publication_type, @publisher_id = a.publisher_id FROM IHarticles a, MSpublications p, master..sysservers s WHERE a.name = @article AND p.publication = @publication AND a.publication_id = p.publication_id AND a.publisher_id = s.srvid AND UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default IF @table_id IS NULL or @article_id IS NULL BEGIN RAISERROR(21615, 16, -1, @article) RETURN (1) END -- -- Parameter Check: @operation. -- The operation can be either 'add' or 'drop'. -- IF LOWER(@operation) NOT IN (N'add', N'drop') BEGIN RAISERROR (14019, 16, -1) RETURN (1) END -- -- Check if there are snapshot or subscriptions and raiserror if needed. -- EXECUTE @retcode = sys.sp_MSreinit_article @publication = @publication, @article = @article, @need_new_snapshot = 1, @need_reinit_subscription = 1, @force_invalidate_snapshot = @force_invalidate_snapshot, @force_reinit_subscription = @force_reinit_subscription, @check_only = 1, @publisher = @publisher, @publisher_type = @publisher_type IF @@ERROR <> 0 OR @retcode <> 0 return (1) -- -- Column must exist -- IF @column is NOT NULL BEGIN IF NOT EXISTS ( SELECT * FROM IHpublishercolumns WHERE table_id = @table_id AND name = @column ) BEGIN RAISERROR(21616, 16, -1, @column, @article) RETURN (1) END IF LOWER(@operation) = N'drop' BEGIN Declare @isset int -- Determine column id of column SELECT @columnid = publishercolumn_id FROM IHcolumns WHERE name = @column AND article_id = @article_id IF @@error <> 0 OR @columnid IS NULL BEGIN raiserror(21209, 16, -1, @column) return (1) END -- Primary key column may not be dropped for TRAN publication IF @publication_type = 0 BEGIN exec @isset = sys.fn_IHiscolpk @columnid if NOT @isset = 0 BEGIN raiserror(21250, 16, -1, @column) return (1) END END END -- drop -- @ignore_distributor is set to 1 when removing replication forcefully. In that -- case, no need to check or reinit -- Check if there are snapshot or subscriptions and raiserror if needed. EXECUTE @retcode = sys.sp_MSreinit_article @publication = @publication, @article = @article, @need_new_snapshot = 1, @need_reinit_subscription = 1, @force_invalidate_snapshot = @force_invalidate_snapshot, @force_reinit_subscription = @force_reinit_subscription, @check_only = 1, @publisher = @publisher, @publisher_type = @publisher_type IF @@ERROR <> 0 OR @retcode <> 0 return (1) END -- Get default mappings in one shot to improve perf -- The cost to get all columns is nearly the same as getting just one. -- Error on the side of getting all the columns because this will be a big win in many cases IF (@publisher_type LIKE N'ORACLE%') BEGIN INSERT INTO @coldatamap (column_name, column_ordinal, dest_datatype, dest_length, dest_precision, dest_scale, dest_nullable, dataloss) SELECT column_name, column_ordinal, dest_datatype, dest_length, dest_precision, dest_scale, dest_nullable, dataloss FROM sys.fn_ORAenumarticlecolumninfo(@publisher_id, @table_id, @article_id, @publisher_dbms, @publisher_version, 1) colinfo ORDER BY colinfo.column_ordinal END -- FUTURE: Other types should use a similar method begin tran save TRANSACTION articlecolumn IF @column IS NULL BEGIN DECLARE artcolumn CURSOR LOCAL FAST_FORWARD FOR SELECT ihpc.name, ihpc.publishercolumn_id, ihpc.column_ordinal, ihpc.type, cdm.dest_datatype, cdm.dest_length, cdm.dest_precision, cdm.dest_scale, cdm.dest_nullable, cdm.dataloss FROM IHpublishercolumns ihpc LEFT OUTER JOIN @coldatamap cdm ON ihpc.column_ordinal = cdm.column_ordinal WHERE ihpc.table_id = @table_id -- AND ihpc.column_ordinal = cdm.column_ordinal ORDER BY ihpc.column_ordinal END ELSE BEGIN DECLARE artcolumn CURSOR LOCAL FAST_FORWARD FOR SELECT ihpc.name, ihpc.publishercolumn_id, ihpc.column_ordinal, ihpc.type, cdm.dest_datatype, cdm.dest_length, cdm.dest_precision, cdm.dest_scale, cdm.dest_nullable, cdm.dataloss FROM IHpublishercolumns ihpc, @coldatamap cdm WHERE ihpc.table_id = @table_id AND ihpc.column_ordinal = cdm.column_ordinal AND ihpc.name = @column ORDER BY ihpc.column_ordinal END OPEN artcolumn WHILE (1=1) BEGIN FETCH artcolumn INTO @artcolumn, @publishercolumn_id, @column_ordinal, @src_type, @dest_type_name, @dest_length, @dest_prec, @dest_scale, @dest_nullable, @dataloss IF @@fetch_status <> 0 BREAK IF LOWER(@operation) = 'add' BEGIN -- Check if this column is already published, if it is silently ignore it IF EXISTS ( SELECT * FROM IHcolumns WHERE article_id = @article_id AND name = @artcolumn ) BEGIN CONTINUE END -- Check if data type is publishable IF (sys.fn_MSrepl_checktype(@src_type, @publisher_dbms, @publisher_version) = 0) BEGIN RAISERROR(21669, 10, -1, @artcolumn, @src_type) CONTINUE END -- Get core type (necessary due to varchar(max) cases) IF UPPER(RIGHT(@dest_type_name, 5)) = N'(MAX)' BEGIN SELECT @dest_type_name = LEFT(@dest_type_name, LEN(@dest_type_name) - 5) SELECT @dest_length = -1 END -- Get systype id for destination data type SELECT @dest_type = type_id(@dest_type_name) IF @dest_type IS NULL BEGIN RAISERROR(21779, 16, -1, @src_type) IF @@trancount > 0 BEGIN ROLLBACK TRANSACTION articlecolumn COMMIT TRAN END RETURN (1) END -- Insert the column to mark it as being published for this article INSERT IHcolumns ( name, publishercolumn_id, article_id, column_ordinal, mapped_type, mapped_length, mapped_prec, mapped_scale, mapped_nullable ) VALUES ( @artcolumn, @publishercolumn_id, @article_id, @column_ordinal, @dest_type, @dest_length, @dest_prec, @dest_scale, @dest_nullable ) IF @@ERROR <> 0 BEGIN if @@trancount > 0 begin ROLLBACK TRANSACTION articlecolumn commit tran end RETURN (1) END END ELSE BEGIN -- operation is 'delete' -- Error if column to drop is a primary key on a TRAN pub IF @publication_type = 0 BEGIN EXEC @ispk = sys.fn_IHiscolpk @publishercolumn_id IF @ispk = 1 BEGIN RAISERROR (21250, 16, -1, @column) IF @@trancount > 0 BEGIN ROLLBACK TRANSACTION articlecolumn COMMIT TRAN END RETURN (1) END END -- Remove the column from the article DELETE FROM IHcolumns WHERE article_id = @article_id AND name = @artcolumn IF @@ERROR <> 0 BEGIN if @@trancount > 0 begin ROLLBACK TRANSACTION articlecolumn commit tran end RETURN (1) END END END -- WHILE -- Update distributor timestamp to reflect change in meta data EXEC @retcode = sys.sp_IHreplflush @publisher IF @retcode != 0 OR @@ERROR != 0 BEGIN if @@trancount > 0 begin ROLLBACK TRANSACTION articlecolumn commit tran end RETURN (1) END -- Set publisher_status to 0 to indicate -- that a call to sp_articleview is needed -- to complete the article definition. UPDATE dbo.IHarticles SET publisher_status = 0 WHERE article_id = @article_id AND table_id = @table_id IF @@ERROR <> 0 BEGIN if @@trancount > 0 begin ROLLBACK TRANSACTION articlecolumn commit tran end RETURN (1) END -- Have to call this stored procedure to invalidate existing snapshot or reint -- subscriptions if needed EXECUTE @retcode = sys.sp_MSreinit_article @publication = @publication, @article = @article, @need_new_snapshot = 1, @need_reinit_subscription = 1, @force_invalidate_snapshot = @force_invalidate_snapshot, @force_reinit_subscription = @force_reinit_subscription, @publisher = @publisher, @publisher_type = @publisher_type IF @@ERROR <> 0 OR @retcode <> 0 BEGIN IF @@trancount > 0 BEGIN ROLLBACK TRANSACTION articlecolumn COMMIT TRAN END RETURN (1) END COMMIT TRAN END
No comments:
Post a Comment