Cursor behavior from SQL 2000 to SQL 2005

• rrenn007

  SSC Rookie

  Points: 26

  More actions

  October 13, 2009 at 12:50 pm

  #138762

  Hi all,

  One database on SQL 2000 had a stproc which used a cursor that called another stproc that also had a cursor. This ran very well.

  Now that database was upgraded to SQL 2005 and the stproc doesn't even finish executing. It runs an endless loop.

  We have found that adding STATIC to the cursor has resolved the issue in sql 2005, but Im not finding anywhere WHY this is the case? Did sql 2005 change their default cursor behavior from STATIC in 2000 to Dynamic in 2005? Why now much you explicitly state this in the cursor definition?

  Any help is appreciated.

• rrenn007

  SSC Rookie

  Points: 26

  More actions

  October 13, 2009 at 2:12 pm

  #1065387

  I have found the answer via a user's bug submission.

  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311452#details

  Implicit cursor conversions SQL 2000

  http://msdn.microsoft.com/en-us/library/aa172581(SQL.80).aspx

  Implicit cursor conversions SQL 2005

  http://msdn2.microsoft.com/en-us/library/ms190641.aspx