• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

How can I document on error rollback or commit?

Please see the image and answer its question with proper arguments.
-.bmp
0
midfde
Asked:
midfde
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, that is because the errors you get abort the whole batch, so the code does actually not continue to run for the second sections...
0
 
midfdeAuthor Commented:
Where do you know it from please (did I mentioned 'proper argumentation'?)?
In fact I am interested in convincing output about whether the transaction was committed or rolled back and how to make T-SQL work as it is supposed to according to ACID .
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I forgot that. this tech note is for the CATCH TRY, but the extract below applies to your "problem":
http://msdn.microsoft.com/en-us/library/ms175976.aspx

Errors Unaffected by a TRY…CATCH Construct
TRY…CATCH constructs do not trap the following conditions:
....
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

   
Compile errors, such as syntax errors, that prevent a batch from running.
...
These errors are returned to the level that ran the batch, stored procedure, or trigger.


your error is indeed a compile error, and hence make the transaction as a whole is not run (because compilation fails)
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.

 
midfdeAuthor Commented:
>>your error is indeed a compile error
This is a false statement. Query-->Parse (ctrl F5) works fine. It would be a compile error if it had something like "pprint" instead of "print". In this case the query would not even start, but in my case as you can see it succeeds in the previous and subsequent statements.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I can tell you that it "is" a compile error.
the issue is that the DROP and CREATE constraint being in the same batch, it will try to "compile" the CREATE CONSTRAINT while it actually still exists.

try to run the DROP and CREATE in 2 batches.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but I can understand your problem... "compile" error is eventually not the 100% accurate term. but it's the most appropriate term I could find in the past years for this behavior
0
 
midfdeAuthor Commented:
Thanks. Conclusion: Check @@error at the beginning of the next batch.
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now