Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Why DECLARE c CURSOR does not roll back?

Posted on 2011-02-23
8
Medium Priority
?
748 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34961423
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34961438
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
ID: 34961512
After your rollback statement use....

CLOSE c
DEALLOCATE c

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:midfde
ID: 34961653
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 34962480
>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
ID: 34962678
I'd agree wholeheartedly, but... why does NOT declaration "Declare @i as int " require DEALLOCATE @i?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34963340
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
ID: 34963823
Yes, it does not, but error 16915 exists and is raised even across GO batch boundaries.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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