Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.

I had two SQL Servers running on two separate machines , both running SQL 2000, under same domain.
This SP was running on one of the machine, linked to the other server called NT1, everything was running fine.

NT1 was upgraded to SQL 2008 64 Ent , and i am getting this error now ?

I need some input ASAP ,, thanks


CREATE proc prwq58_Updatejan10
AS
declare @guid uniqueidentifier
declare @patkey varchar(50)
declare @jan10 int
declare crStatus cursor for
      select guid,patkey, jan10 from nt1.arc.dbo.natalog where guid in (
      select guid from wq58_pg1 where complete = 1 and guid in (
      select guid from wq58_pg2 where complete = 1 and guid in (
      select guid from wq58_pg3 where complete = 1 and guid in (
      select guid from wq58_pg4 where complete = 1 and guid in (
      select guid from wq58_pg5 where complete = 1 and guid in (
      select guid from wq58_pg6 where complete = 1 and guid in (
      select guid from wq58_pg7 where complete = 1 and guid in (
      select guid from wq58_pg8 where complete = 1 and guid in (
      select guid from wq58_pg9 where complete = 1 and guid in (
      select guid from wq58_pg10 where complete = 1 and guid in (
      select guid from wq58_pg11 where complete = 1 and guid in (
      select guid from wq58_pg12 where complete = 1 and guid in (
      select guid from wq58_pg16 where complete = 1 and guid in (
      select guid from wq58_pg17 where complete = 1 and guid in (
      select guid from wq58_pg19 where complete = 1 ))))))))))))))) and (jan10 <> 63 or jan10 is null)
open crStatus
fetch next from crStatus into @guid,@patkey,@jan10
while (@@fetch_status <> -1)
begin
      
      if(@jan10 <> 60 and @jan10 <> 91)
      begin
            insert into wq58_jan10(guid,patkey,jan10) values(@guid,@patkey,@jan10)
      end
      exec nt1.arc.dbo.prwq58jan10Update @guid
      fetch next from crStatus into @guid,@patkey,@jan10
end
close crStatus
deallocate crStatus
Slider_ictAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might try this rewrite:
CREATE proc prwq58_Updatejan10
AS
declare @guid uniqueidentifier
declare @patkey varchar(50)
declare @jan10 int
declare @t table (guid uniqueidentifier, patkey varchar(50), jan10 int )

insert into @t select guid,patkey, jan10 from nt1.arc.dbo.natalog where guid in (
      select guid from wq58_pg1 where complete = 1 and guid in (
      select guid from wq58_pg2 where complete = 1 and guid in (
      select guid from wq58_pg3 where complete = 1 and guid in (
      select guid from wq58_pg4 where complete = 1 and guid in (
      select guid from wq58_pg5 where complete = 1 and guid in (
      select guid from wq58_pg6 where complete = 1 and guid in (
      select guid from wq58_pg7 where complete = 1 and guid in (
      select guid from wq58_pg8 where complete = 1 and guid in (
      select guid from wq58_pg9 where complete = 1 and guid in (
      select guid from wq58_pg10 where complete = 1 and guid in (
      select guid from wq58_pg11 where complete = 1 and guid in (
      select guid from wq58_pg12 where complete = 1 and guid in (
      select guid from wq58_pg16 where complete = 1 and guid in (
      select guid from wq58_pg17 where complete = 1 and guid in (
      select guid from wq58_pg19 where complete = 1 ))))))))))))))) and (jan10 <> 63 or jan10 is null)

while @@rowcount > 0
begin
  select top 1 @guid = guid, @patkey = patkey, @jan10 = jan10 
     from @t
  if @@rowcount > 0
  begin 
      if(@jan10 <> 60 and @jan10 <> 91)
      begin
            insert into wq58_jan10(guid,patkey,jan10) values(@guid,@patkey,@jan10)
      end
      exec nt1.arc.dbo.prwq58jan10Update @guid
      fetch next from crStatus into @guid,@patkey,@jan10
  end

  delete @t
   where guid = @guid
    and patkey = @patkey
    and jan10 = @jan10
end

Open in new window

0
Slider_ictAuthor Commented:
that helped but this fixed my problems, just adding INSENSITIVE in cursor definition


declare crStatus INSENSITIVE cursor for

Thanks for you input .
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.