Ignore Duplicate Over Linked Server

Advertisement

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