I have a 2005 sql server which is linked to 2000 sql server. I am trying to run an update query on the 2000 database. it does get updated but it takes a long time to run even one single row. I have defined a primary key on the destination table. I checked the collation too. I have the same problem as this post, only that the solutions there is not solving mine.
I had the same error. Now I am able to get rid of the error, but the query to update a single row is 4 minutes.
declare @tblSource table
(intapplicantid int primary key,
insert into @tblSource
select intapplicantid, txtstatus from tblapplicants
where intapplicantid = 124963
set dest.txtstatus = src.txtstatus
from @tblSource src
on dest.intapplicantid = src.intapplicantid
I am using temp table just to check the performance.
Is anything obvious in the above query? Please help!