.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Replication and auto Identity Increment

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
We want to have two SQL server working on the same data at the same time (SQL Server 2008). I've been told that the auto ID's will stop working (the identity option). I'm not sure how true this is or what is the best replication to use for this.   Thanks

View Complete Post

More Related Resource Links

T-SQL how set identity seed on auto increment +1


hi everybody

I have stored procedure where i am creating table and setting primary key how to set auto increment ?

set @sql =@sql + 'CONSTRAINT [PK_'+@Table+'] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]'

Thanks for any idea

SharePoint Batch Update Auto Increment Field Value


Hello All,

I am making use of SharePoint List Batch Updating methodology in order to update the items in bulk. I build the query dynamically for each and every item using StringBuilder and process the data ultimately using 'ProcessBatchData' Method.

Now, I have a requirement where I have to increment a particular field value in the query while iterating through each item for building the query. How can I auto-increment a field value for each item in the batch query?


Auto Increment User defined Id

Hi can anyone give me an idea how to create an auto generated id like ED01,ED02 etc., so that when i am entering data the id should be automatically incremented

auto increment date

hi all, I have a table CLRYR with two columns STARTYR,  ENDYR I need to insert value into this these columns as below using a stored procedure STARTYR   ENDYR 1/1/2010 1/31/2010 1/1/2011 1/31/2011 1/1/2012 1/31/2012 i should be able to give the start yr and lets say i have to insert 50 yrs from now. So the values in these columns should add up upto 01/01/2060  01/31/2060 so i will have 50 records with the startyr and endyr. Please help me. thank you.

SQL auto increment in two tables

Hi, i have a problem with auto increment. I generated auto ID with auto increment, but after this i need to insert new ID to other table. Is possible to generate same ID to both tables? Is possible to do it without select command?

a simple way to auto-increment INT by 1 ?

Hi,   I have a field of datatype INT declared as follows  [barCodeID] int IDENTITY(1, 1) NOT NULL Each time I insert a new record into the table, barCodeID increments by 2, not by 1. Why so and can I make it increment by 1? I also tried [barCodeID] int IDENTITY(0 , 1) NOT NULL which produces exactly the same result, so what the first digit(seed) actually defines ? Thanks -- as you can see I'm a complete newbie

Why are Identity columns automatically marked as "NOT FOR REPLICATION" for non-updateable Transactio

Hi, Replication has been a blind stop for me. I've always tried to avoid it. Anyway, I've got (non-updatable) transactional replication of data from a production database (SQL2008 R2) into a reporting database (SQL2005 SP3). There are a handful of tables with Identity columns. Looking at the Article table properties of the tables in question, I can see that "Identity Management" is set to Manual (and greyed so not changeable). Also the Identity columns of the source tables are now marked as NOT FOR REPLICATION. When I look at the replica tables, I can see that they also have an Identity on the Primary Key column. Looking at the "sp_MSins" procedures, I can see that the procedure does include the column with the Identity on it in the INSERT statement. So, having look at all this, I'm more confused that before. But, the number 1 vitally important thing for me is that the values in the Identity columns are "honoured" when replicated to the reporting database (i.e. as if I used the -E flag of BCP). And my first question is, is this the case? I've examined the data from a couple of tables, and currently it appears to be true, but I'm worried that this is just luck. Secondly, as the insert procedure includes the Identity column in the Insert (the the Identity is present in the replica), I'm assuming / guessing that the Distribution agen

@@IDENTITY issues created in MS Access after a Merge Replication

Hi all, We have 2 SQL Servers that update each other using Merge Replication. We then connect to the tables via an ODBC link within Access and this worked without any problems until we upgraded our SQL Server from 2000 to 2008 R2. Since the upgrade we are having problems within Access. When we come to add a new record, the record returned is different to the one we added. After much research we discovered its down to the triggers within SQL Server (from versions 2005 onwards) where the trigger updates the global variable @@IDENTITY. We've also found out that MS Access uses this variable to return the record that was supposedly last added but since it gets altered as part of a merge trigger it makes Access problematic. With this being a very big issue with MS Access and SQL Server Merge Replication triggers I can assume that others would have managed to work around this and come up with a solution. can anyone help?

Identity Range Value (Max Limit Finished) SQL Server 2005 (Merge Replication Problme)


Hi ,

I am using SQL Server 2005 enterprise edition, I Confirured the Merge Replication to my database). I have a replication of 21 database from various locations.

Problem is: When is trying to remove one subriction from the publisher and add a fresh subriciton to it. it is poping any error saying the Identity Range value exceed its Max Limit try to change from int to bigint. is there any solution for it not changing from int to bigint.

Thank you.


auto increment of primary key which is varchar


hi everyone ...

i  was given a form in which empid is varchar  and i m supposed to auto increment it using a  function and calling it ....

i have never done this. plz help me out the way .

Replication and Identity


I am a starter and newbie to SQL Server. Currently, I am developing SQL MERGE replication database with Mobile Devices. Publication DB has several tables for replication. one table has Identity column.
When Insert Query was run, I got the error.

"09/21/2010 04:00:00,POD Generate Manifest,Error,1,SRV01,POD Generate Manifest,Generate Manifest,,Executed as user: NT AUTHORITY\SYSTEM. The insert failed. It conflicted with an identity range check constraint in database 'MobilePOD'<c/> replicated table 'dbo.Delivery'<c/> column 'DeliveryID'. If the identity column is automatically managed by replication<c/> update the range as follows: for the Publisher<c/> execute sp_adjustpublisheridentityrange; for the Subscriber<c/> run the Distribution Agent or the Merge Agent. [SQLSTATE 42000] (Error 548) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.,00:00:01,16,3621,,,,0"

In the Constraint, there is

whose expression is
"([DeliveryID]>(54009) AND [DeliveryID]<=(55009) OR [DeliveryID]>(55009) AND [DeliveryID]&l

transactional replication with identity column


Dear friends,

I have a transactional replication between A and B where A replicates its data into B . the schema of A and B are EXACTLY same. I have a table "t" in both databases which has an IDENTITY column with name "c" and it is set to be NOT FOR REPLICATION in both databases. Indeed I have "A.t.c " and "B.t.c " columns. Also I should mentioned that I never do any changes in B.t table. It should just get the last data from A.t (but because of some reasons I need to have a same schema).

I do the following steps :

1- Add a new row to A.t . (and see the added row in B.t after a few seconds either )

2- update this added row's c2 column (not the c which is  the identity and PK column)

3- an exception I receive in Replication Monitor which says I cannot update the B.t.c column because it is an Identity column!!!... (I checked the related MS's related update stored procedure)...

the question is why it wants to update the Identity column which is not needed indeed!?

Replication without Identity


Hi all,

I have a replication scheme where we have transactional (snapshot) replication between Publisher and Subscriber.

Subscriber should never be modified (IE: no inserts/updates/deletes) that need be synchronized at the Publisher.  The subscriber essentially contains a subset of publisher tables that are used by a UI in a read only fashion.

Now, the publisher tables have identity columns.  The client insists that the replication can replicate the tables to the subscription and not copy over the identity column (IE:  When the snapshot is loaded ... the tables in the Sub are dropped and re-created ... Boom ... we have an identity column)

Is there ANY way to load the initial snapshot and NOT have to column copied over but NOT as an identity column????

I have not seen anything in the publication properties that would prevent the column from being copied over as identity.

Any ideas ... anyone ... Bueller?

Thanks for any information ...

Transactional Replication Identity column difference in SQL 2008


Hello All,

I have a Pull Transactional Replication in SQL 2000.

To enable the copying of Identity columns, my subscriber Insert stored procs are modified to mark


In the end, I set it to Off.

The environment is stable for 2 years. Now we are upgrading to SQL 2008.

Do I still need to do the above step?

I have noticed that SQL 20088 is even updating Subscriber table for schema changes also.

I am not sure if MS took care of that issue in SQL 2008.

Please let me know your inputs.



do not see auto increment after insert


hello all,


i'm a newbee, so maybee a simple question

i have a parent table, with , lets say an id, and another field

i have another table, the child table which uses the id of the parent, for the master-detail relation.

the id is defined as datatype int, allow nulls = no, unique = yes, primary key = yes, identity=true, identityIncrement=1, identitySeed=1

so the id is auto incremented.


i am using ado.net and have a SqlCeDataAdapter, and SqlCeCommand to set InsertCommand

when inserting data and calling the Update() method of the SqlCeDataAdapter, reload the table, i see a nice id.

but, before the table is updated, the Id field is empty, and i would like to see what the id is, because i have also a child table which uses this id.

so, how can i see the id, or how can i add the values to the child, and use a reference (the id or something else) before updating the table ? or is it better to use another technique. I would like to work with the dataset because the data is loaded in memory, and will not save everytime the changes. 

if you know some nice samples, please let me know.

thanks ! andy 


auto increment column in SQL View



Can you please show me what is the syntax to add an increment column to Select Query? There will be additional column which goes 1,2,3 ... n. 

Document ID auto increment


I have a standard document library that I'm adding documents to.  I would expect that the 'ID' property to auto-increment by 1 each time I upload a document, but the column is being incremented by 5.  Is this a setting I can change somewhere to have it increment by 1 instead?  Thanks.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend