May 11, 2010 at 7:55 am
Hey!
I am having a simple but critical problem. My scenario is I have a table with two columns, the first one is integer type, Identity column + Primary Key and the second column is a datetime type. I have to replicate the table through transactional replication to a subscriber where the same schema exists. This is many to one architecture i.e. The subscriber is subscribed to different servers\databases for the table. The replication only have insert statement and no update\delete follows.
The problem is I want to replicate the table as such that the data when is replicated for insert, the subscriber doesn't take the identity value of the publisher database and works with its own identity insert. I can't change my primary key to the second column as well.
Any one has any idea how to go for it?:unsure:
May 11, 2010 at 8:01 am
I think i understand the issue, in order to prevent the repliaction agent from incrementing the column when it does an insert use the NOT FOR REPLICATION option
May 11, 2010 at 8:11 am
I thought of the same and changed the schema to the publisher and subscriber side ,added NOT FOR REPLICATION keyword. But later I found out that it doesn't do what I have stated. It just instead of general insertion of the identity column, paste the column so that both publisher and subscriber are at sync. I don't require this column to be sync as I am not doing any updates\deletes. :exclamationmark:
Thanks for replying but it doesn't work out.
May 12, 2010 at 3:41 am
Yes looking at it again i don't think would help you sorry, i guess the issue is that you are trying to change the primary key and the replication agent needs the primary key to indentify the rows to update/add/delete. You mentioned that you can't change the primary key to the second column, But is it possible to add a third column with a GUID that can act as your primary key?
Also depening on how complex the system is, you could possibly use SSIS to acheive the same results and this would allow you to manage your own primary key.
May 12, 2010 at 3:51 am
You could try having the identity key on one side all the even numbers and the identity on the other all the odd numbers. in that way the numbers shouldn't clash, ensure that the identity increments by 2.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply