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
LVL 1
midfdeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>My question was "Why?"
because DECLARE is only a declarative statment. it will not allocate any resources (yet), only the OPEN cursor will actually allocate the cursor and it's associated resources

as to the documentation, see here:
http://msdn.microsoft.com/en-us/library/aa258831%28v=sql.80%29.aspx

Remarks
DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and FETCH returns a row from the result set. The CLOSE statement releases the current result set associated with the cursor. The DEALLOCATE statement releases the resources used by the cursor.


so, the DECLARE only defines what the cursor will be... when opening
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
rollback does indeed roll back changes ... but it does not erase the cursor C being declared.

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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to reformulate, just to be 100% clear:

DECLARE is not a resource USED, just a declaration.
OPEN cursor will start USING the resource.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
EvilPostItCommented:
After your rollback statement use....

CLOSE c
DEALLOCATE c

Open in new window

0
 
midfdeAuthor Commented:
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?
0
 
midfdeAuthor Commented:
I'd agree wholeheartedly, but... why does NOT declaration "Declare @i as int " require DEALLOCATE @i?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
midfdeAuthor Commented:
Yes, it does not, but error 16915 exists and is raised even across GO batch boundaries.
0
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.

All Courses

From novice to tech pro — start learning today.