sql server replication: Replication with identity key failing -- sql server replication

RE: Replication with identity key failing

Anis
2/27/2007 12:03:31 PM

I forgot to mention that i do NEED the SID key to be a identity key on the
destination table

[quoted text, click to view]

Re: Replication with identity key failing

Anis
2/27/2007 2:54:13 PM

Thanks Hillary. I got past that part with your help.

Now however when my replication goes through i get error 2601 (can not
insert duplicate key)

Let me explain, i have multiple databases replicating to a central server. I
have a key setup for a serial number in the tables. When the serial number is
dplicated in multiple tables i get that error. What i want to do is to have
the replication succe3es and overwrite the serial number (i want the most
uptodate entry stored in the central server)

Is there anyways to do this? Note that i have the profile set to skip such
errors, so replication proceeds, but it would be nice if i can get the latest
record replicated rather than have it skip it.

Thanks again for your help

[quoted text, click to view]

Re: Replication with identity key failing

Hilary Cotter
2/27/2007 3:52:19 PM

You have to comment out the update of the identity column portions of your
sp's. By definition identity properties cannot be updated.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Re: Replication with identity key failing

Hilary Cotter
2/27/2007 9:24:35 PM

You will have to do some existence check in your insert proc, where you
check to see if it exists and if it does update, if it doesn't then do an
insert.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Re: Replication with identity key failing

Anis
2/28/2007 7:50:05 AM

Hillary

Thanks a lot, i think i am close. Although i am not really experienced into
the proc scripts.

can you give me a quick example?

right now my insert proc looks like this

create procedure "sp_MSins_summary_end_systems" @c1 int,@c2 varchar(16),@c3
varchar(128),@c4 varchar(128)

AS
BEGIN

// I am guessing this is where the check would go

insert into "__framduser"."summary_end_systems"(
"ESID", "Serial_No", "IP_Address", "ReportingServer"
)

values (
@c1, @c2, @c3, @c4
)

END

GO
create
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.