Solved

Why DECLARE c CURSOR does not roll back?

Posted on 2011-02-23
8
656 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:midfde
  • 4
  • 3
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
to reformulate, just to be 100% clear:

DECLARE is not a resource USED, just a declaration.
OPEN cursor will start USING the resource.
0
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
After your rollback statement use....

CLOSE c
DEALLOCATE c

Open in new window

0
 
LVL 1

Author Comment

by:midfde
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
>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
 
LVL 1

Author Closing Comment

by:midfde
Comment Utility
I'd agree wholeheartedly, but... why does NOT declaration "Declare @i as int " require DEALLOCATE @i?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:midfde
Comment Utility
Yes, it does not, but error 16915 exists and is raised even across GO batch boundaries.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now