June 14, 2006 at 8:00 am
Hello I have a table which looks like this :
CREATE TABLE [INFO_EXPORT_MOVEMENT] (
[WMS_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ItemCode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_ItemCode] DEFAULT ('*'),
[Quantity] [int] NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_Quantity] DEFAULT (0),
[MovementDate] [datetime] NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_MovementDate] DEFAULT (getdate()),
[MovementType] [tinyint] NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_MovementType] DEFAULT (0),
[LocationFrom] [tinyint] NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_LocationFrom] DEFAULT (0),
[LocationTo] [tinyint] NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_LocationTo] DEFAULT (0),
[RefEbly] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_RefEbly] DEFAULT ('*'),
[RefSupplier] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_RefSupplier] DEFAULT ('*'),
[Comment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_EXPORT_MOVEMENT_Comment] DEFAULT ('*')
) ON [PRIMARY]
GO
The table is filled by a trigger on another table, which insert
ItemCode, Quantity, MovementType, LocationFrom, LocationTo, RefEbly, RefSupplier, Comment
I Have this table on 2 server running a merge subsciption. This table used to be part of this subscription, but I removed it. I stopped the merging, modified the publication on the publisher, created a new snapshot and restarted the merging.
Al went ok. On the first server the WMS_ID is growing as it should be. Every record a 1 is added to previous value.
On the second server however.....the WMS_ID looks like this:
1210487391 KA783 69450 2006-06-14 09:51:14.380 6 3 2 0 0 348740009000089361
1210487391 139 6500 2006-06-14 12:00:36.820 1 7 2 2737 00601796 348740009000091364
1210487391 449 6200 2006-06-14 12:00:36.837 1 7 2 2737 00601796 348740009000091371
1210487391 B6077 720 2006-06-14 13:14:45.943 1 7 2 161 348740009000091456
1210487391 139 12000 2006-06-14 11:59:36.617 1 7 2 2737 00601796 348740009000091357
1210487391 c509 6125 2006-06-14 11:59:36.617 1 7 2 2737 00601796 348740009000091333
1210487391 139 12000 2006-06-14 11:59:36.617 1 7 2 2737 00601796 348740009000091340
1210487391 A5999 690 2006-06-14 13:51:52.030 1 7 2 161 348740009000091500
1210487391 KT847 73200 2006-06-14 09:54:14.787 6 3 2 0 0 348740009000087411
1210487391 KT845 92400 2006-06-14 10:02:16.090 6 3 2 0 0 348740009000088005
1210487391 A5999 -700 2006-06-14 13:50:51.810 1 7 2 161 348740009000091494
It looks like there is still a link with the table on the first server, And that the WMS_ID is a duplicate record victim or so..I had this fenomenen also when the table was part of the replication.
Does someone has any idea about this ?
June 15, 2006 at 2:34 am
When you put identity tables in replication you should create the table with including following command
[IDENTITY [(seed, increment) [NOT FOR REPLICATION]
As your table is in merge replication it’s possible that insert happens on both the ends at the same time so the chances of getting the same identity on both the server are more.
The simple solution what we implemented in our case is.
On publication server we start the seed with 1
On subscriber server we start the seed with some big number like 60,000,000
By following this you won't get duplicate id problem.
------------
Prakash Sawant
http://psawant.blogspot.com
June 15, 2006 at 1:31 pm
Hello,
the table is no longer in in replication. so I'm wondering
1 - why is it always the same number
2 - Were is the value coming from, the starting value = 1 step 1....
June 16, 2006 at 12:57 am
It's possible that while your table was in replication the identity got replicated & now after you remove the replication the identity is out of replicated but started from same last value which got replicated, so you will see always same value on both the side when ever you insert a new record in these tables as the seed may be same on both the side.
You can reseed the identity in one of the table & give some big number to start with, after which you will come to know if the identity is actually getting replicated.
------------
Prakash Sawant
http://psawant.blogspot.com
June 16, 2006 at 1:06 pm
What I forgot to tel is that on the other server, the identity increments as it should....
And stiil if it is an identity it should count up, not stay the same value
??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply