April 30, 2012

sp_IHarticlecolumn (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews