SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
'***************************************************
' Purpose :Add Identity range to the user defined Tables under a given publisher
' Inputs :Publisher Name and Identity Range
' Returns :None
' Author :Mahesh M Kodli
'****************************************************
CREATE PROCEDURE AddMergeArticle
@pPublisherName VARCHAR(255),
@pIdentityRange BIGINT
As
DECLARE @tRV INT
DECLARE @tArticle VARCHAR(255)
SET @tRV = 0
DECLARE Merge_Article_Cursor CURSOR FOR
--Get all the user defined Tables for which to add Identity Range
SELECT TABLE_NAME TableName FROM INFORMATION_SCHEMA.TABLES WHERE rtrim(ltrim(table_type))='BASE TABLE'
AND TABLE_NAME Not like 'Conflict%' AND TABLE_NAME Not like 'dtproperties'
AND TABLE_NAME Not like 'sys%' AND TABLE_NAME Not like 'MS%'
OPEN Merge_Article_Cursor
FETCH NEXT FROM Merge_Article_Cursor INTO @tArticle
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if the Table has identity column and accordingly set the Auto
--Identity Range to TRUE or FALSE Before adding Identity Range to the
--article
IF OBJECTPROPERTY ( OBJECT_ID(@tArticle), 'TableHasIdentity') = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name = @tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE name like @pPublisherName AND UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
BEGIN
--Use the System stored procedure add merge article to add Identity range for -- each article
EXECUTE sp_addmergearticle @publication = @pPublisherName, @article = @tArticle, @source_owner = N'dbo', @source_object = @tArticle, @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'true', @pub_identity_range = @pIdentityRange, @identity_range = @pIdentityRange, @threshold = 80, @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0,@force_invalidate_snapshot=1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name = @tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE name like @pPublisherName AND UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()))
BEGIN
EXECUTE sp_addmergearticle @publication = @pPublisherName, @article = @tArticle, @source_owner = N'dbo', @source_object = @tArticle, @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'False', @pub_identity_range = NULL, @identity_range = NULL, @threshold = NULL, @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0,@force_invalidate_snapshot=1
END
END
FETCH NEXT FROM Merge_Article_Cursor INTO @tArticle
END
CLOSE Merge_Article_Cursor
DEALLOCATE Merge_Article_Cursor
IF (@@ERROR <> 0)
BEGIN
SELECT @tRV = -95 --UnSuccessful
GOTO XIT
END
XIT:
RETURN @tRV
