Why DECLARE c CURSOR does not roll back?
Please answer the question that you may see on the attached image. Give me a reference that indicates the explanation is not just a personal opinion.
My question is inspired by e.g., the following excerpt from BOL:
"ROLLBACK TRANSACTION or ROLLBACK WORK
Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed".
-.bmp
My question is inspired by e.g., the following excerpt from BOL:
"ROLLBACK TRANSACTION or ROLLBACK WORK
Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed".
-.bmp
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
to reformulate, just to be 100% clear:
DECLARE is not a resource USED, just a declaration.
OPEN cursor will start USING the resource.
DECLARE is not a resource USED, just a declaration.
OPEN cursor will start USING the resource.
After your rollback statement use....
CLOSE c
DEALLOCATE cASKER
I understand what I can do. My question was "Why?". And then, if a declared cursor is not a resource (that as we know must be freed by DEALLOCATE), then what is a resource? How can I tell apart "thingies" that are and are not resources to be freed by ROLLBACK.
And again, what documentation "thinks" about it please?
And again, what documentation "thinks" about it please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'd agree wholeheartedly, but... why does NOT declaration "Declare @i as int " require DEALLOCATE @i?
actually, even the cursor does not "require" that.
once the script has finished, as then the variable/cursor goes out of scope, it will be removed from the definition stack.
once the script has finished, as then the variable/cursor goes out of scope, it will be removed from the definition stack.
ASKER
Yes, it does not, but error 16915 exists and is raised even across GO batch boundaries.
so, you have 2 options:
* keep 1 declare cursor c ... (if both are indeed the same), and just close and reopen the cursor
* declare 2 different cursors.