Cursor issue, @@fetch_Status turned out as -2

• nic2885

  SSC-Addicted

  Points: 479

  More actions

  June 22, 2011 at 11:32 am

  #242938

  I want to execute a SP in all databases, I have 40 Dbs on a server,

  Cursor executes, sometimes for 6 dbs, while sometime for 21. there is no fix time for which it executes,

  Query turns out successful but output is not for all DBs.

  When i checked, select fetch_status from sys.dm_exec_cursors (0) I get output as

  -2. Please find part of the code below. I have declared all the other variables -

  Please suggest!

  begin

  Declare scriptor cursor static local for select name [@mDatabase] from master..sysdatabases order by name desc

  open scriptor

  fetch next from scriptor into @mdatabase

  while @@fetch_status=0

  Begin

  exec s_ScriptAllDatabases

  @SourceUID = 'galupload',

  @SourcePWD = 'tsg2000',

  @OutFilePath = @mOutFilePath ,

  @OutFileName = @mOutFileName ,

  @WorkPath = @mWorkPath , -- no spaces

  @SourceSVR = @mSourceSVR,

  @Database = @mDatabase

  fetch next from scriptor into @mdatabase

  select @@fetch_status

  end

  Close scriptor

  Deallocate scriptor

  END

• nic2885

  SSC-Addicted

  Points: 479

  More actions

  June 23, 2011 at 11:36 am

  #1343271

  Hi sorry guys,

  I got the solution in the above script i have declared cursor as Static and local.

  Be4 doing this i was getting error, 1s i declared it as static and local, it worked.

  Thanks anyways