.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

Replcation -> Subscriber no Identity on Columns

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

Hi All,


My publisher database has identities set on columns.

We load the snapshot into the subscriber by dropping all the tables and creating them as new.

We are concerned with bringing over the identities on these columns may cause issues.  We would like to just copy the same values from the publisher to the subscriber.


Are we being overly concerned about something we do not need to?  Is it guaranteed to be the same value copied over, even if it is an identity column?

Please advise.

Thank you for any information.

Phil Lamey


View Complete Post

More Related Resource Links

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.


Combining databases with Identity columns

(Using SQL Server 2005)  I have only a basic knowledge of SQL Server, but need to roll up 5 SQL databases into one database (I guess this is known as "replication"?).  These database are of similar structure and make use of an identity (or autonumber) column.  These id's are also referenced in numerous child tables.  To avoid collisions, this is my plan:   1.  turn off the identity feature of all the columns (but still keep condition of uniqueness) 2.  For each database, assign a unique range of identities (ie, db1 gets 1-500, db2 gets 501-1000, etc) and then renumber these columns accordingly.  The new id's should cascade to the child tables via the table relationships. 3.  Replicate or copy the data of all 5 databases into the new one.  There should be no collisions. 4.  Turn back on the identity feature of the appropriate columns, hopefully everything works as before. After this is done we hopefully can get rid of the 5 original databases and proceed with just one. Should this work and/or is there a better way to do this?  

Mark columns as Identity in multi-table views?

Hello! When you create a view of one table (i.e. SELECT * FROM Table), it marks the table's identity column as an identity col in the view as well. I have a few that selects columns from several nested tables, but they are all related in a main identity column I select as well, but SQL Server doesn't mark this column as identity, is there a way to do it?Shimmy

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

Getting list of tables with Identity Columns



I'm new to SQL Server, could somebody help me with the query for

Getting the list of tables with indentity columns i.e to check the key info in the system tables along with column data types.

Thanks in Advance


join two identity columns


I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.
Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.

Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

How to prevent Propagation from subscriber to Publisher in some occations

We are using merge application. We need sometimes delete some tables at subscriber and that delete command or any other command  should not propagate to Publisher and other subscribers. And sometimes it should be refilled from Publisher. Is it possibe? Second question : My publisher crashed two times. I could attach the database after restoring the OS. But at subscriber I am unable to modify some table and it gives the error invalid object name 'dbo.msmerge.......'. Replication is working fine. What is the reason for this error and how I can get rid of it?

One subscriber in the two replications, containing the same table. How can this be

There are 2 merge publications (MS SQL 2005)  that use the same table but with different filters. Each of them has their subscribers, but I accidentally discovered that one subscriber is contained in both publications. How such a scheme might work, it is unclear what will happen in the case of reinitialization etc. Should I alter the scheme that this was a subscriber in one publication, or everything will work?

Subscriber data changes problems when use_partition_groups set to false

Hello,Within our solution we use merge replication to replicate data between MS SQL Server 2008 (running on MS Windows Server 2008) and MS SQL Server Compact 3.5 (running on Windows XP Tablet PC). Initially we set the parameter @use_partition_groups of sp_addmergepublication to true. The replication worked well but later we have observed problems while inserting/updating records on publisher. (commands have been timed out) The timeout problem has been solved by setting @use_partition_groups to false - the performance on subscriber is good, but (as we later realized) subscribers are not getting the changes properly (Publisher’s deletes are not reflected at subscriber) We have found following thread however the answer did not help us: http://social.msdn.microsoft.com/Forums/fi-FI/sqlreplication/thread/638bc057-b12f-4e04-b42d-564b244c715fWe would need to solve this problem ASAP.Please help.Thx a lot in advanceRobert

SQL Server 2005 as Distributor, Subscriber - SQL Server 2008 as Publisher

Hi,           I am creating a Distributor on SQL Server 2005 the server name is Server1 and Server1 is the Subscriber as well, we are using Push Subscription           Publisher is on SQL Server 2008, Server2. When i try to create a Publications, it throws the following error.          Server "Server1" is an Older Version of SQL Server and cannot be the Distributor for Server  'Server2'  Server1 - Distributor, Subscriber  Server2 - Publisher Is there any way to overcome this issue..? Regards, Fazlu.

error while restoring database at subscriber

Hi ALL,             I have merge replication configured between 2008 publihser and 2000 subscriber. I was trying to restore database at subscriber and reinitialize it...While i am trying to restore database at subscriber its failing with error System.Data.SqlClient.SqlError: Invalid column name 'retention_period_unit'. (Microsoft.SqlServer.Smo)   any suggestions TIA

Delivering Replicated Transactions ? No update on subscriber

I set up a Transactional Replication on 2 Standard 2008 SQLSERVERs on LAN - one was publisher and also distributor - another one was the subscriber All options are default. My SQL Replication has worked normally for 5 days until yesterday. Periodically, i checked the data on subscriber manually, there are no update for subscriber for 12 hours. I tried to View Synchronization Status and also Replication Monitor. Everything seem fine excepting the below status didn't change for a long time. "View Synchronization Status  :  Delivering Replicated Transaction" I tried to test the latency of distributor and subscriber by inserting a Tracer in Tracer Tokens of Replication Monitor. The latency of "Publisher to distributor" was 00:00:01, but the latency of "Distributor to Subscriber " and "Total latency" were "Pending". It still was "Pending" after 30 minutes. -The biggest transaction of my system just updated 35.000 records/time. It's not big enough to stuck the replication. -There is no trigger on the subscriber.   1.Any suggestions so far ? Thanks 2.The status is "Delivering Replicated Transactions" . So, where to see the current transactions that have been delivering by Distributor ? The one that caused system stuck. Not all. The highest one in agent queue

change subscriber instance

Is it possible to change the instance of a subscriber? I have a subcription for which the instance cannot be reached by the server\instance, it needs to have the port information appeded or it will not connect

replication; what is: SqlCeReplication.Subscriber?

I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC.  In my code I have a SqlCeReplication object.  One of the properties that must be set is: Subscriber, but I have no idea what that should be set to.  When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber".  Any help will be appreciated.

How to auto create (or Generate a script) which can create indexes on subscriber


In our scenario, subscriber and publishers have same tables and we want the indexes also to be same at all places..

How to auto create (or Generate a script) which can create indexes on subscriber which are already added on publisher.. Is there a auto way or is there any article somewhere which can help..


IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

Latency Alerts from SQL Server 2008 (Distributor and Publisher) and SQL Server 2005 (Subscriber)



     We are using Transactional Replication with Distribution and Publisher in One Server (Ex: ServerA) which is SQL Server 2008 and Subscriber Server is (ServerB) which is SQL Server 2005. Can anyone let me know how to set the Latency Alerts. If Possible please provide the link which will explains step by step.

Thanks in Advance



The merge process detected a mismatch while evaluating the subscriber partition validation expressio



We have a Merge Replication system between SQL 2008 R2 Enterprise and Express edition. 2 servers are on the same domain, and everything works fine by sql replication.

When we want to synchronize by web synchronization, we get an error like below

"The merge process detected a mismatch while evaluating the subscriber partition validation expression. The problem can be resolved by
reinitializing the subscription." (we did reinitializing but same error again!)

and also this another error message in same situation

" - The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up.
 - The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3."

Any idea?


Best regards.


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