sql server replication: Biggest Problem (Very imp scenario for all of u, u must aware of
this....problem in sql server 2000)

Here I have a different scenario in Merge Replication using SQL SERVER 2000
on Win2000 Server/Win 2000Adv Server/NtServer (all)

DB1.Table1
Identity Seed =1
Identity Increment =2
(auto number should be generated as odd numbers)
Assume this data bydefault
Column1 (AutoNo) Column2 (any char datatype)
1 A1
3 A3

DB2.Table2
Identity Seed =2
Identity Increment =2
(auto number should be generated as even numbers)
Assume this data bydefault
Column1 (AutoNo) Column2 (any char datatype)
2 B1
4 B3

After Merge Replication, I received the following output both for Table1 and
Table2 which is fine
DB1.Table1 (1,2)
Column1 (AutoNo) Column2 (any char datatype)
1 A1
3 A3
2 B1
4 B3

DB2.Table2 (2,2)
Column1 (AutoNo) Column2 (any char datatype)
1 A1
3 A3
2 B1
4 B3
Here is a question :
Now, I add some data at DB2.Table2. Note down next identity value in Column1
DB2.Table2 (2,2)
Column1 (AutoNo) Column2 (any char datatype)
1 A1
3 A3
2 B1
4 B3
5 B4

Here, according to identity seed=2 and identity increment=2
Why I am receiving another id=5. However I was expecting it to be 6 b/c I
have identified seed=2 and increment=2(even number not an odd number).....

Thanks and Regards
Asad Imam
Pakistan.

I think reseed of identity values using "DBCC CHECKIDENT" should help you.
I am using following stored procedure to reseed all db tables.


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_ReseedIdentity]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_ReseedIdentity]
GO

CREATE PROCEDURE dbo.sp_ReseedIdentity
@even bit
AS
BEGIN

SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF

DECLARE l_hSqlCursor CURSOR LOCAL FAST_FORWARD
FOR (
SELECT '
DECLARE @CURR_IDENT INT
SELECT @CURR_IDENT =
CASE WHEN ISNULL( MAX( ' + C.NAME + ' ), 0 ) % 2 = 0
THEN ISNULL( MAX( ' + C.NAME + ' ), 0 )' +
CASE WHEN @even = 1
THEN ' + CASE WHEN ISNULL( MAX( ' + C.NAME + ' ), 0 ) = 0 THEN 2 ELSE
MAX( ' + C.NAME + ' ) END'
ELSE ' + 1'
END + '
ELSE ISNULL( MAX( ' + C.NAME + ' ), 0 )' +
CASE WHEN @even = 1
THEN ' + 1'
ELSE ''
END + '
END
FROM '+ U.NAME + '.' + O.NAME
+ ' DBCC CHECKIDENT( '''
+ U.NAME + '.' + O.NAME
+ ''', RESEED, @CURR_IDENT )'
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
ON O.ID = C.ID
JOIN SYSUSERS U
ON OBJECTPROPERTY( O.ID, 'OWNERID' ) = U.UID
WHERE U.NAME != 'dbo'
AND O.XTYPE = 'U'
AND COLUMNPROPERTY( O.ID, C.NAME, 'IsIdentity' ) = 1
)

-- Declare FETCH variables
DECLARE @RESEED_CMD VARCHAR(500)

-- Open SQL cursor
OPEN l_hSqlCursor

-- SQL variables
DECLARE @l_nError INT
SET @l_nError = 0

WHILE ( @l_nError = 0 )
BEGIN
-- Fetch next record
FETCH NEXT
FROM l_hSqlCursor
INTO @RESEED_CMD

IF (@@FETCH_STATUS <> 0 ) -- handle errors
BEGIN
IF ( @@ERROR = 0 )
PRINT 'Procedure succeeded'
ELSE
PRINT 'Procedure failed'
BREAK
END

--------------------------------

-- Do processing
EXEC ( @RESEED_CMD )
--PRINT ( @RESEED_CMD )

--------------------------------

IF ( @@ERROR <> 0 )
BEGIN
PRINT @@ERROR
BREAK
END

END

CLOSE l_hSqlCursor
DEALLOCATE l_hSqlCursor

SET IMPLICIT_TRANSACTIONS ON
SET NOCOUNT OFF

END
GO


--
_______________________
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
E-mail: Kestutis.Adomavicius@bss.biz
Phone: +370 69911787

[quoted text, click to view]

One more remark. This sp is reseeding only tables owned not by dbo, but it
can be changed of course :)

--
_______________________
Kestutis Adomavicius

A couple of additional pieces of information would be helpful. When
defining the merge article are you using the @auto_identity_range set to
'true' or 'false' (default)? When defining the merge subscription, are you
using @sync_type='none' or 'automatic' (default).

Based on what I see below, the following should work to set up odd values on
the publisher and even values on the subscriber. A possible solution
requires you to manually manage the identity values on both sides and it
will require the merge subscription to use the No Sync feature --
@sync_type='none'.

Create your DB1 (publisher) and DB2 (subscriber) databases. Create the
table in both databases, identity (1,2) in DB1 and identity (2,2) in DB2.
Make sure you also define the Rowguid column in both tables as well. This
is required by merge.

create table t1 (
Column1 int identity (1,2) NOT FOR REPLICATION,
Column2 varchar(100),
rowguid uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__t1__rowguid__74AE54BC] DEFAULT (newid()))
go

It looks like the merge subscription in your original scenario is set to
'automatic', so the merge agent will reseed the subscriber upon the first
merge while applying the initial snapshot. You need to use
@sync_type='none' for the merge subscription. This will prevent the merge
agent from reapplying the publisher table at the subscriber and thus
maintain your identity (2,2) on the subscriber. You need to initially
populate the subscriber database.

A drawback to watch out for is that you will have a difficult time adding
more subscribers to your replicaiton topolgy because only odd and even
numbers are allowed. If more subscribers are needed, you may need to break
this down and start over.

Another drawback is that the @sync_type option is set at the subscription
level. This means all articles in the publication pointed to that
subscription are in No Sync mode. You would have to initially sync all
articles manually. A workaround for this would be to create two
publications, one with your identity table using a No Sync subscription and
another publication with all other articles that sync automatically.

Please read through the following topic in SQL 2000 BOL -- "Managing
Identity Values". If you can conform to the requirements of the automatic
identity range management it may be a better solution for you going forward.
Instead of using odd and even numbers, your would use different ranges of
identity values on the publisher and subscriber.

--
Sincerely,
Tom Michaels
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]