.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 without Identity

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

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 ...

View Complete Post

More Related Resource Links

Replication and auto Identity Increment

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

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.


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!?

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.



Merge Replication - Publisher Identity Range Assignment with Disconnected Subscriber


Hello everyone,

I have a merge replication topology setup as follows...  Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2 (RTM), Subscriber - SQL 2008 SP1.

I recently had my subscriber disconnected for a couple days.  During this time, I was getting the following error on my publisher:

The insert failed. It conflicted with an identity range check constraint in database 'production', replicated table 'dbo.CustomerOrder', column 'CustomerOrderId'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

I understand why I would normally get this error, but if the publisher and distributor are both online, why didn't the publisher attain a new range from the distributor after it hit its threshold?  Is this expected behavior whenever a subscriber is disconnected?

Thanks in advance.


After Merge Replication Identity column was not generated Properly


HI All,


    I has been implemented Merge replication in sql server 2008, in that some tables Id locums are add 2000 and 20000 no add automatically incremented, why it was happens how to resolved this issues? if i want to get continues number what will it do, please help me in this ..... this is very urgent for me.




MSSQL Transactional Replication (Identity Seed)



Im doing a migration from server A to server B.

I used transactional replication. Everything replicated nicely, however, the primary key constraint. Identity had auto resetted to 1. (which is not correct).

The table was created as "NOT FOR REPLICATION" <-- is this the main cause? I cant do anything to the database, as i'm not the application owner.


    [abrev] [varchar](50) NOT NULL,
    [converted] [varchar](50) NOT NULL,
    [longtext] [varchar](50) NULL,
    [texttype] [varchar](50) NOT NULL,
    [abrev_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [abrev_id] ASC

now, how do i save the situation? should i mirror the table structure, then do a replication for data?

please advise.

thanks Shawn

Pass ASP.NET membership identity object across domain?


I am trying to figure a way to SSO with ASP.NET membership and role model.

I can implement custom membership provider which consume web services hosting on a server.

With encryption of data. No problem.

But my question is, If I sign on a website with my provider, got my identity object on that application,

could I pass it to another website on another domain which use the same provider and by doing so, do not need to login again?

If this is possible, I am going to implement this solution. Please tell me what's your take on this.

Federated Identity: Passive Authentication for ASP.NET with WIF


The goal of federated security is to provide a mechanism for establishing trust relationships between domains. Platform tools like Windows Identity Foundation (WIF) make it much easier to support this type of identity federation. We show you how.

Michele Leroux Bustamante

MSDN Magazine August 2010

AD FS 2.0 in Identity Solutions: Using Active Directory Federation Services 2.0 in Identity Solution


This article explains how you can use Active Directory Federation Services (AD FS) 2.0 to claims-enable Windows Communication Foundation (WCF) services and browser-based applications. The focus is on the token issuance functionality in AD FS 2.0. You'll find out how to use AD FS 2.0 as an identity provider; set up an AD FS 2.0 security token service (STS) to interact with WCF; federate AD FS 2.0 with your custom STS or another AD FS 2.0; enable Web single sign-on and federation with WS-Federation and SAML 2.0 protocols; and externalize authentication logic through Visual Studio. You'll come away appreciating how AD FS 2.0 and Windows Identity Foundation make programming identity solutions in Windows less of a chore.

Zulfiqar Ahmed

MSDN Magazine November 2009

Security Briefs: Exploring Claims-Based Identity


Keith Brown introduces you to the new identity model in the Microsoft .NET Framework 3.0.

Keith Brown

MSDN Magazine September 2007

Identity: Secure Your ASP.NET Apps And WCF Services With Windows CardSpace


Windows CardSpace replaces traditional authentication with a more consistent and streamlined login process and improves trust between end-users, applications and services. Michèle Leroux Bustamante explains.

Michele Leroux Bustamante

MSDN Magazine April 2007

Aajaxtoolkit control +

dear all,   i have propblem, when i put  <identity impersonate="true" userName="domain\username" password="password" />   i have the following erro: Could not load file or assembly 'AjaxControlToolkit' or one of its dependencies. Access is denied.   but if i remove this tag : <identity impersonate="true" userName="domain\username" password="password" /> this site work normally. and i need to put this tag in web.config.
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