When setting up a (merge) publication using the wizard, you can click
the three-dotted button in the "specify articles" and then setup the
identity range for publisher and subscribers.
While this is an interesting option, I see that the default is not
checked (yes, I have the identity column setup as not for replication).
Now my database only has about 50 tables but nevertheless, not my
favorit waste of time to call up each table one by one and adjust these
values one by one (tabbing to the correct tab, checking the option,
adjusting the range-values).
So, is there any way to get this checked by default (that would be one
step forward) and preferably also adjust the values while we're at it?
Many thanks in advance,
Ferry
Ferry,
there's no way that I'm aware of. One posibility is to script out the
publication and use a find and replace in notepad to make the alteration
then drop the original and recreate the publication using this script -
admittedly not nice but would save a lot of leg-work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||From Paul Ibison :
> Ferry,
> there's no way that I'm aware of. One posibility is to script out the
> publication and use a find and replace in notepad to make the alteration then
> drop the original and recreate the publication using this script - admittedly
> not nice but would save a lot of leg-work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thanks Paul. I guess I'll have a look and see if I can do either that
or perhaps find some 'backdoor'...
Ferry
|||From Ferry :
> From Paul Ibison :
> Thanks Paul. I guess I'll have a look and see if I can do either that or
> perhaps find some 'backdoor'...
> Ferry
Having said that, I started a more detailed search on Google and came
up with this one. Going to check this later:
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
|||Ferry,
thanks for the heads up. I've found the link
http://www.devarticles.com/c/a/SQL-S...2000-Part-2/3/
and will add it onto my site. The only problem with the proc above is that
it assumes you want to replicate all tables, so there's room for another
(simpler) version which takes a tablename as a third parameter.
To have this occur without any intervention such as using Mahesh's script
you would have to edit the stored procedure sp_addmergearticle itself to
hardcode the automatic range management. This is a posibility but which
obviously invalidates support agreements - depends on how confident you are
of getting it spot on
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
No comments:
Post a Comment