Ignore Duplicate Over Linked Server

Hello,



I am trying to insert rows to a table with a unique index that has the ignore duplicate property.

My program is running fine if running locally but the entire transaction failed if it is over a linked server.

The following is an example:



create table t1 (c1 int,c2 int)

create unique clustered index i1 on t1 (c1) with IGNORE_DUP_KEY





if running locally :

select count(*) from t1
go

insert into t1 values (1,2)
insert into t1 values (1,2)
insert into t1 values (2,2)
insert into t1 values (1,2)

go

select count(*) from t1
go



The output is:



(1 row(s) affected)


(1 row(s) affected)

Server: Msg 3604, Level 16, State 1, Line 3
Duplicate key was ignored.

(1 row(s) affected)

Server: Msg 3604, Level 16, State 1, Line 5
Duplicate key was ignored.

(1 row(s) affected)


and the count(*) returns 2 at the end.



If running over a linked server:

select count(*) from linkserver.db.dbo.t1
go

insert into linkserver.dbarchive.dbo.t1 values (1,2)
insert into linkserver.db.dbo.t1 values (1,2)
insert into linkserver.db.dbo.t1 values (2,2)
insert into linkserver.db.dbo.t1 values (1,2)

go

select count(*) from linkserver.db.dbo.t1
go



The output is:



(1 row(s) affected)


(1 row(s) affected)

Server: Msg 3604, Level 16, State 1, Line 3
Duplicate key was ignored.


and there is only one row in the table !



Any clue how to avoid that?



Eyal