CODEWITHSUNDEEP
sql-servert-sqlsql-updatelinked-server
Xi Vix
Xi Vix

Reputation: 1361

Too Many Rows Were Affected error on updating a Transact SQL linked table

I am trying to update a linked table with this...

update openquery (LINKSERVERID, 'select tng_email from user_list where tng_id = 62873') 
  set tng_email = '[email protected]';

... but I get the following error ...

OLE DB provider "MSDASQL" for linked server "LINKSERVERID" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".

FYI: tng_id is the primary key.

How do I fix?

Upvotes: 3

Views: 6460

Answers (3)

Kevin Davidson
Kevin Davidson

Reputation: 141

I solved this error by adding a unique index to the underlying table.

Upvotes: 0

Attie Wagner
Attie Wagner

Reputation: 1362

I tried the answer above from jpw, but it didn't work for me.

I developed a trigger of which I also received the same error using the code below:

begin

    if TRIGGER_NESTLEVEL() > 1
    return

    declare @JCid       int =   (select top 1   iJCMasterID from inserted)

    begin
        update L set uiJCTxCMLineNumber = NewLineNum
        from (
        select
            rank() over (order by idJCTxLines) NewLineNum
        ,   iJCMasterID
        ,   uiJCTxCMLineNumber
        ,   idJCTxLines
        from    _btblJCTxLines
        where iJCMasterID =  @JCid
        ) L
        where   iJCMasterID =  @JCid
    end
end
go

However, I resolved it by changing it to :

begin

    if TRIGGER_NESTLEVEL() > 1
    return

    declare @JCid       int =   (select top 1   iJCMasterID from inserted)

    begin
        update L set uiJCTxCMLineNumber = NewLineNum
        from (
        select
            rank() over (order by idJCTxLines) NewLineNum
        ,   iJCMasterID
        ,   uiJCTxCMLineNumber
        ,   idJCTxLines
        from    _btblJCTxLines
        where iJCMasterID =  @JCid
        ) L
    join inserted i on L.idJCTxLines = i.idJCTxLines
    end
end
go

Hope this helps.

Upvotes: 0

jpw
jpw

Reputation: 44871

I think you need to include the key in the select query, so try this:

update openquery(
  LINKSERVERID, 'select tng_id, tng_email from user_list where tng_id = 62873'
) set tng_email = '[email protected]';

Upvotes: 8

Related Questions