Identity Seed Gone Bad In Merge Replication

Aug 8, 2007

Sever: sql 2000
Replication: Merge

Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"

I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.

From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."

When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.

Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.

My question is what could cause the identity seed to get set to such a high number??

any thoughts would be great!

View 2 Replies


ADVERTISEMENT

Identity And Merge Replication

Jan 31, 1999

Dear all,

How does an identity is affected by merge replication? Can sql control it so that there will be no replication conflict?

Here's the scenario, I have several tables with an identity column. the database resides in a server in the head office, there are several remote sites and remote users which will be running sql 7 desktop edition. Each remote will download only the data subset he is allowed to. Each site will be allowed add new rows on several table. Then they will update the head office server through replication.


Thanks

Reden

View 1 Replies View Related

Merge Replication Identity Ranges

Apr 5, 2008

I have a database table with a simple identity column. As a starting point... when I run the following queries... this is what I get:

SELECT Max(AliasID) FROM Account_Managers
1300006

DBCC CHECKIDENT ('Account_Managers')
Checking identity information: current identity value '1300006', current column value '1300006'.


Now... I set up merge replication, with the plan of allowing SQL Server to manage my identity ranges for me. I set up my ranges to be 100000 on the publication, 100000 on the subscriber, with a threshold of 80.

I would EXPECT to see the constraint
([AliasID]>(1300006) AND [AliasID]<=(1400006)) on the publisher and
([AliasID]>(1400006) AND [AliasID]<=(1500006)) on the subscriber.

However... what I do get is:

([AliasID]>(1300006) AND [AliasID]<=(1400006) OR [AliasID]>(1400006) AND [AliasID]<=(1500006)) on the publisher and

([AliasID]>(1500006) AND [AliasID]<=(1600006) OR [AliasID]>(1600006) AND [AliasID]<=(1700006))


Can anyone tell me why I get the OR... which essentially makes my identity ranges twice as large as they should be?

Thanks in advance.

- Alan D. Nelson

View 2 Replies View Related

Replication :: Merge Identity Value Out Of Sync

May 16, 2015

We are running merge replication. My identity value was out of sync and the inserts stopped working. I Reseeded the identity value to 1500000 and ran "sp_adjustpublisheridentity" which set that to the new range (and table constraint) in the MSMerge file and table. I discovered my error and Reseeded the table back to 150000. How to I get the new range to be created when it is LESS than 1500000 in the MsMerge file???? Can I use the sp_restoreidentity....???

View 3 Replies View Related

SQL 2012 :: Merge Replication - Identity Columns

Nov 3, 2015

I was reading this:

"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."

I don't understand...

If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?

Suppose I have a this table:

1 Name1
2 Name2
3 Name3

Column 1 is identity field and column 2 the name of employees.

If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?

What about if the identify fields is a primary key?

View 6 Replies View Related

Merge Replication Identity Range Error

Jan 8, 2008

Hi all i have setup merge replication with one publisher and 3 subscribers



The replication worked fine for about 2 months then i recieved the following error on all my subscriptions



The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity
ranges to allocate to its own Subscribers or when an identity column data type does
not support an additional identity range allocation. If a republishing Subscriber has
run out of identity ranges, synchronize the republishing Subscriber to obtain more
identity ranges before restarting the synchronization. If a Publisher runs out of identit
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417


I have updated my sql server to service pack 2

The publisher is sql Standard edition and the subscribers are Express edition



Any help would be great

Thanx

View 3 Replies View Related

Identity Primary Field In Merge Replication

Jul 23, 2007

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.



What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e



On Server 1 - Values start from 1 then 2,3,4 etc etc



Server 2 - 24001, 24002, 24003 etc etc



Server 3 - 46001, 46002, 46003 etc etc



Server 4 - 68001, 68002, 68003



My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?



Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.



cheers

View 1 Replies View Related

Can I Set A Default Range For The Identity Values For Merge Replication?

May 8, 2006

Hello,

We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.

View 1 Replies View Related

Merge Replication - Setting Identity Range Management.

Feb 2, 2007

Hi All;

I am trying to set up Merge Replication on a database and want to set the IdentityRange Management to Auto for all my tables which use a Identity column.

In the wizard, on Article Properties Page, I can do this by selecting a Table, and going for its properties, but this is a tedious task as I have ~300 tables to set this property on.

Is there another way or a global location where I can set the property to true and even mention the ranges and the threshold, so that I dont have to pick each table and set it individually.

I am also aware of the fact that I can Generate a Script and modify it and run that, but I was looking for some way to do this in the wizard.

Thanks!

View 1 Replies View Related

Merge Replication With Anonymous Subscribers And Identity Columns

Dec 21, 2005

Hi,

I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?

Thanks,
Darrell Young

View 1 Replies View Related

Identity Seed

Dec 11, 2007

Hi guys,Please is it possible in SQL SERVER any version (Prefferably 2005) to set my Identity seed to the current year so that when we are in 2008 it continues from 2008 ?
Best Regards

View 2 Replies View Related

SQL Identity Seed

Nov 5, 2003

Hello-

I am auto generating an id when a record is input into my database.
I know how to set the id field to create an identity seed for each new entry with an increment of 1.

Is there anyway to add characters to begining of this identity seed. Can it only be an integer.
For example I would like my id field to be in the following format:

NCID - 1
NCID - 2
.
.
.
.

Can anyone show me how to do this if possible?
Thanks

View 2 Replies View Related

Identity Seed

Nov 9, 2000

I have a table with an identity seed in it. I need to go back and get some history that wasn't loaded into it. I would then like to resequence the identity column. Is this possible? Can I use the dbcc checkident? or will this just start any future inserts with the number I specify. I would like to reseed the whole file when I'm done starting with 1 and going forward is this possible with this command.

View 2 Replies View Related

Identity Seed

Oct 12, 2000

Is there a way to drop the seed or sequence and recreate it? Thank you very much.

View 2 Replies View Related

Identity Seed

Sep 17, 1999

How can i reset the identity seed of a counter field to 1 after deleting
records from a table. Even though the identity seed displays 1 in the design
mode, when i add another record the counter field is incremented to the next
number from the last deleted record.

thanks
Todd Minifie

View 3 Replies View Related

Identity Seed

Sep 15, 2006

hello there

Just a quick question. (sql 2000)

Can you have and auto increment identity seed for a table that always ends with say a predefined Letter?

i.e.

1a , 2a, 3a, 4a, 5a, etc...

View 2 Replies View Related

Identity Seed

Jul 20, 2005

Can I change the value of a column's identity seed programmatically? Ifso, how?Thanks in advance.

View 2 Replies View Related

Edit SQL Identity Seed

Jan 9, 2004

Because of testing and deletion, my table of user groups starts at 15 or so. Now I want to insert a group for administrators but I would like to have the groupID be 1. I tried to turn off the identity seed property and insert it manually but that didn't work.

Is there any way to do this?

Thanks, Dave

View 3 Replies View Related

Reset IDENTITY Seed

Aug 26, 2005

Hello,

Can I reset the IDENTITY seed of a Table column without delete/drop the table?

I want to delete all the table rows, restore de seed, and restore a
backup made on a XML (using SET  IDENTITY_INSERT Table ON)

I cant drop the table due to acount restricctions.

regards,
Edu

View 3 Replies View Related

Incerement The Identity Seed

Jan 27, 2003

Hi,

I have a table which has a field called x which is set to identity with seed 1 and incerement 1.

Currently the table has 100 records so the last value of x is 100.

I would like to adavnce the seed number to be 1000, so that the next insert into the table will be 1001...

This is possible to do through the Enterprise manager, but I would like a script to do it.

Does anyone know where such a script exists?

What I actually need is a script that creates a CREATE TABLE Statement for the table (including all info - columns,indexes etc...).

thanks
Moshe

View 3 Replies View Related

Reset Identity Seed

Jun 1, 1999

I would like to set the identity seed to a different value. How do I do that? Please help!

Sam

View 3 Replies View Related

Identity Seed Issue

Apr 4, 2008

Hi ..

I have an issue here. I create a DB and some tables through a script in SQL Server. I have lot of tables in DB and quite a lot have identity columns with seed set to 1 and increment set to 1. The scripts executed fine and all the tables created. Now when I do the first insert records into the tables the identity column associated starts with 0 even though the seed is set at 1. Its the case with all the tables where the identity column are set. The first records into all these tables starts with 0 for all the identity columns.

I could'nt figure out what is causing this issue ..

Any fix for the issue ..

thanx in advance

View 1 Replies View Related

Identity Seed Issue

Apr 4, 2008

Hi ..

I have an issue here. I create a DB and some tables through a script in SQL Server. I have lot of tables in DB and quite a lot have identity columns with seed set to 1 and increment set to 1. The scripts executed fine and all the tables created. Now when I do the first insert records into the tables the identity column associated starts with 0 even though the seed is set at 1. Its the case with all the tables where the identity column are set. The first records into all these tables starts with 0 for all the identity columns.

I could'nt figure out what is causing this issue ..

Any fix for the issue ..

thanx

View 2 Replies View Related

Identity, Seed, Increment

Aug 25, 2005

Iam trying to add a column to an existing table that would be an "identifier". I called it "ReadingNumber" and selected Identity "Yes" and "Identity increment" as 1.
When I add it, it just gives the rows random numbers instead of by the order they were inserted into the database by.....is there a way to autonumber the columns correctly? I have a COLUMN called Date and also one called Time that have the date and Time, but the format is char.
Would I have to convert the date time columns into something SQL understands, sort them ASC or DESC and then do the Identity column add?
Thanks for any help

Edited

View 15 Replies View Related

Identity Seed Questions

May 15, 2006

My client has a need for the auto identity field to be 6 digits in length starting with the number 001000. They want the leading 0's preserved since this will be a casenumber. Even if I set the identity seed to 001000 it gets rid of the leading 0's. How can I get it to keep those?

View 1 Replies View Related

Identity Seed Reset In SQL Table

Feb 16, 2004

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

Thanks

View 5 Replies View Related

IDENTITY - Using A Local Variable Value For A Seed Value

Mar 22, 1999

I'm trying to have an identity column seed value specified with a local variable value as follows, however it
doesn't allow me to do it (Says cannot use a variable name for a seed value).
Any ideas or suggestions?

DECLARE @idvalue int

SELECT @idvalue = max(accountid) + 1
FROM account

CREATE TABLE accounttemp
(Accountid int IDENTITY(@idvalue,1),
name char(10),
address char(10))

View 1 Replies View Related

Identity Column-- Seed/Increment Value

Oct 1, 1998

I have created a table that generates a sequential id and a
stored procedure that will return that id. The trouble is
no matter what I set the Seed or Increment values to, the
id will always start with #1 and increment by 1.

My table is BILLING_TIME_ID
Identity field BT_GEN_ID
(SEED 200, INCREMENT 1)

The sp is as follows:
CREATE PROCEDURE BT_NEXT_ID
AS
INSERT dbo.BILLING_TIME_ID DEFAULT VALUES
select count (*) from dbo.BILLING_TIME_ID
GO
I have double checked that Identity_Insert is set to off for
this table. (does this default to off unless it is set to on?) Since
there is only 1 field in the table, I don`t have any indexes set.

Thanks for your help!
Toni

View 1 Replies View Related

Using A Variable To Assign An IDENTITY SEED

Feb 16, 2004

I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

View 5 Replies View Related

How To Avoid Repeating Identity Seed

May 9, 2008

Hi,

I've just recently learned that being an identity seed-column doesn't guarantee that you will always get unique values. It can double up and cause a violation of PK. If so, is there a work around this that doesn't involve a REINDEX? Cause if im home and my client suddenly experiences this in the middle of a busy day, that would be a total disaster. Any ideas on how i can avoid this or a workaround maybe? Thanks!

View 3 Replies View Related

DTS Packages And Identity Seed (auto-number)

Aug 10, 2001

I am attempting to import data from a Lotus Notes database using DTS. The SQL table I am importing to has an identifying auto-number. I can't insert directly into it because the SQL server should, however I get an error if I ignore it in the DTS package. Is there any way to get around this?

View 4 Replies View Related

Changing Identity Seed And Increment After The Fact

Mar 16, 2004

Hi all,

I've been thrown a curve ball late in the game on an application I'm developing. Without getting into the specifics of the application I store a unique employee ID number for all person records in it. This ID is provided to us by the companies we're servicing. Up until this point in time we didn't have need of an externally visable ID other than the one provided to us. Now, a need for an internally generated (by our application) unique ID has been discovered. This number needs to be a minimum 5 digits (e.x. 10001, 10002, ....). I could achieve this nicely by seeding an identity column at 10000 with an increment of 1. However, I've alredy made different settings for this and there are records in the db with the old ones.

My question is: If I initially set the seed / increment at 1/1 can I change this after the fact without causing data integrity problems. Will all subsequent insertions into this table just start at the new values?

Or, ideally I'd like to create a new column seperate from the PK Identity column already in place that serves the same function, incrementing a 5 digit number by 1 for each new record. Seems that you're only allowed one identity column per table though. Is there another way to achieve get the same result as identity?

Thanks!

View 4 Replies View Related

Change The Seed &&amp; Increment Value Of An Identity Column.

Sep 5, 2007



Dear Sir,


the following code is copied from the SQL Server Help Example.



CREATE TABLE MyCustomers2 (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))

INSERT INTO MyCustomers2 (CompanyName) VALUES ('A. Datum Corporation')

ALTER TABLE MyCustomers2 ALTER COLUMN CustId IDENTITY (200, 2)

It gives the following error.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'IDENTITY'




Can U please guide me abt the error.

with regards,
wilfi

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved