forcing an identity to be even\odd

Hi
I am not able to implement even odd series, the tables
are generating autonumbers with 'last value + 2' for the
next value (inspite of saying identity seed is 2 and
increment is 2). thisway, if the last value on
a 'supposed' even table is turming odd if the last merged
value is odd. is their a way to force newly generated
autonumbers to be only odd in odd table and only even in
even table. something like a formula?
Thanks
Svur
SVur,
this is how I set it up. On the publisher I set the seed at 1 and increment
at 2 to give odd numbers. I replicated this table to the subscriber. After
initialization I dropped the subscription. On the 'subscriber' I changed the
seed and increment to 2 and 2. I recreated the subscriber using the nosync
option and it all worked OK. You could also probably do this more directly
using @.creation_script on sp_addmergearticle.
HTH,
Paul Ibison
|||Hi Paul,
I tried to implement the way you described, but for some
reason, the identity of the new value is always the
current value + 2, irrespective of what the seed is.
I have no clue as to what goes into @.creation_script, i
have not been able to find any articles online aswell.
i would appreciate if you could help me out with this
Thanks
SVur
>--Original Message--
>SVur,
>this is how I set it up. On the publisher I set the seed
at 1 and increment
>at 2 to give odd numbers. I replicated this table to the
subscriber. After
>initialization I dropped the subscription. On
the 'subscriber' I changed the
>seed and increment to 2 and 2. I recreated the
subscriber using the nosync
>option and it all worked OK. You could also probably do
this more directly
>using @.creation_script on sp_addmergearticle.
>HTH,
>Paul Ibison
>
>.
>
|||SVur,
please script out your initial table and I'll have a go at creating a script
for you.
Regards,
Paul Ibison
|||Hi paul,
Please find the script below:
/****** Object: Table [dbo].[Tasks] Script Date:
7/1/2004 11:34:42 AM ******/
CREATE TABLE [dbo].[Tasks] (
[TaskID] [int] IDENTITY (1, 2) NOT FOR
REPLICATION NOT NULL ,
[UnitOfMeasure] [varchar] (70) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TaskDesc] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[GUID] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
Thanks,
SVur
>--Original Message--
>SVur,
>please script out your initial table and I'll have a go
at creating a script
>for you.
>Regards,
>Paul Ibison
>
>.
>
|||Thanks SVur. At the moment we have two threads going essentially the same
issue (hadn't associated your name) so please let's leave this thread and
continue the previous one where I have posted my answer.
Regards,
Paul Ibison

For Loop

I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.

DECLARE @.i INT
SET @.i = 1

WHILE EXISTS(SELECT 1 FROM #Test WHERE id > @.i)
BEGIN
SELECT * FROM #Test WHERE id = @.i
SET @.i = @.i + 1
END

|||

Thank you for the reply Sir. But in the while loop you are giving a select statement. I guess it will perform a table scan which may slow down the process. I am not sure whether I am right or not. If I am right, then is there any alternative condition which can be incorporated in the while loop?

Thanks

Subhojeet

|||Due to the fact that this is a temporary table and not a table variable you can add a index to the table to avoid scans. But keep in mind that in small tables table scans can be faster for the query optimizer than doing a index scan.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Could you please explain what you are trying to do in the loop? You seem to be concerned about the EXISTS query performance but you are doing row-by-row processing which will be slow anyway. If you describe your problem with sample schema/data and expected results it will be easier to suggest a set-based solution.